﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Collections.ObjectModel;
using SQLServerHealthCheck.Model;
using Microsoft.Win32;
using System.IO;
using System.Text.RegularExpressions;
using SQLServerHealthCheck.SQLServer;
using System.Configuration;
using SQLServerOptimizer.PL.WPF;
using WinInterop = System.Windows.Interop;
using System.ComponentModel;
using System.Windows.Media.Animation;
using System.Windows.Threading;
using System.Runtime.InteropServices;
using System.Windows.Controls.Primitives;
using System.Threading;
using AG.Utilities;
using Microsoft.Windows.Controls;
using System.Security.Principal;
using System.Net.Mail;
using System.Net;
using System.Windows.Controls.DataVisualization.Charting;


namespace SQLServerOptimizer.PL.WPF
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    /// 

    public partial class MainWindow : Window
    {

        public ObservableCollection<SQLServerInstance> SQLServerInstances = new ObservableCollection<SQLServerInstance>();
        public ObservableCollection<SQLServerInstance> SQLServerScheduleInstances = new ObservableCollection<SQLServerInstance>();
        public ObservableCollection<SQLServerInstance> PerformanceTraceInstances = new ObservableCollection<SQLServerInstance>();
        public ObservableCollection<SQLServerInstance> ConnectedSQLServerInstances = new ObservableCollection<SQLServerInstance>();
        public ObservableCollection<IssueServer> issueServers = new ObservableCollection<IssueServer>();
        public ObservableCollection<DatabaseTrace> TraceSQLInstances = new ObservableCollection<DatabaseTrace>();

        // public static string strselectedSQLInstance = null;
        public bool traceRefreshSender = false;
        public bool stateOnline;
        public string INt;
        private bool isRunning = false;
        private bool isloaded = false;
        private int OptimizationFinishCount = 0;
        private int InstancesCount = 0;
        int collum;
        int row;
        private bool fisttimeisloaded = false;
        bool datefilled = false;
        private DispatcherTimer TraceStatusCheckTimer = new DispatcherTimer(DispatcherPriority.Normal);

        HealthCheckDataContext dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings.Get("ConnectionStringSSHCDatabase"));

        ICollectionView view;
        public MainWindow()
        {
            this.InitializeComponent();




            this.Loaded += new RoutedEventHandler(win_Loaded);
            this.SourceInitialized += new EventHandler(win_SourceInitialized);
            this.Loaded += new RoutedEventHandler(MainWindow_Loaded);
            this.CommandBindings.Add(new CommandBinding(ApplicationCommands.Help, HelpExecuted, HelpCanExecute));
            this.ResizeMode = ResizeMode.CanResizeWithGrip;


            datacollectionWorker = new DataCollectionWorker(this, ConnectedSQLServerInstances);
            datacollectionWorker.ProgressUpdate += new ProgressUpdateEvent(datacollectionWorker_ProgressUpdate);
            datacollectionWorker.ProccessCompleted += new ProccesCompleted(datacollectionWorker_ProccessCompleted);
            datacollectionWorker.Completed += datacollectionWorker_Completed;
            datacollectionWorker.Cancelled += datacollectionWorker_Cancelled;
            datacollectionWorker.Failed += datacollectionWorker_Failed;
            isloaded = true;
            _statusworker = new ConnectionCheckWorker(this, this.SQLServerInstances);
            _statusworker.InstanceStatusResult += new StatusUpdateEvent(_statusworker_InstanceStatusResult);
            _statusworker.StatusCheckProccessCompleted += new StatusCheckProccesCompleted(_statusworker_StatusCheckProccessCompleted);

            // Start the timer 
            TraceStatusCheckTimer.Interval = new TimeSpan(0, 0, 59);
            TraceStatusCheckTimer.Tick += new EventHandler(ItemContainerGenerator_StatusChanged);


        }

        private ConnectionCheckWorker _statusworker;
        private DataCollectionWorker datacollectionWorker;


        public bool IsAdminRole()
        {
            AppDomain domain = Thread.GetDomain();

            domain.SetPrincipalPolicy(PrincipalPolicy.WindowsPrincipal);
            WindowsPrincipal principle = (WindowsPrincipal)Thread.CurrentPrincipal;
            return principle.IsInRole(WindowsBuiltInRole.Administrator);
        }



        public static bool IsDatabaseCreated()
        {
            string DatabaseCreated = System.Configuration.ConfigurationManager.AppSettings.Get("DatabaseCreated");

            return DatabaseCreated == "true";
        }

        private void HandleDoubleClick(object sender, MouseButtonEventArgs e)
        {

        }

        void MainWindow_Loaded(object sender, RoutedEventArgs e)
        {
            try
            {
                System.Configuration.Configuration oConfig = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
                Logger.LogErrors = bool.Parse(oConfig.AppSettings.Settings["LogErrors"].Value.ToLower());
                Logger.LogDebugInfo = bool.Parse(oConfig.AppSettings.Settings["LogDebugInfo"].Value.ToLower());
                Logger.Initialize(System.Windows.Forms.Application.StartupPath);

                string DatabaseCreated = "";

                DatabaseCreated = oConfig.AppSettings.Settings["DatabaseCreated"].Value;

                if (!IsAdminRole())
                {
                    MessageBox.Show("The SQL Server Optimizer needs to be run as Administrator to use all the functionality.");
                }
                //    "Instance_ListBox" Background="{x:Null}" SelectionMode="Extended"  ItemsSource="{Binding}" ItemTemplate="{DynamicResource InstanceTemp}"
                this.Instance_ListBox.DataContext = this.SQLServerInstances;
                this.Listbox_Schedule.DataContext = this.SQLServerScheduleInstances;
                this.PerformanceTracingListbox.DataContext = this.PerformanceTraceInstances;
                Licensing.Edition = Licensing.GetProductEdition(typeof(MainWindow).Assembly);

                SetVisibilityByProductEdition();

                if (DatabaseCreated.ToLower() == "false")
                {
                    isloaded = true;

                    //Calls the New Database Setup window if the INstance is still set to Local
                    var DatabaseSetupWindow = new DatabaseSetup();
                    DatabaseSetupWindow.Show();
                }
                isloaded = true;
                FillSQLServicesCombobox();
                FillSQLServicesComboboxA();
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }

        }

        public void SetVisibilityByProductEdition()
        {
            if (Licensing.Edition == ProductEdition.Basic)
            {

                TracingRibbontab.Visibility = System.Windows.Visibility.Hidden;
                tabFixes.Visibility = System.Windows.Visibility.Hidden;
                tabScheduling.Visibility = System.Windows.Visibility.Visible;
                Performance_trace.Visibility = System.Windows.Visibility.Collapsed;
                //AddtoScheduling.Visibility = System.Windows.Visibility.Collapsed;
                AddtoScheduling.Visibility = System.Windows.Visibility.Visible;
                tabEmail.Visibility = System.Windows.Visibility.Visible;
                tabSettings.Visibility = System.Windows.Visibility.Visible;
                Settings.Visibility = System.Windows.Visibility.Visible;
            }
            else
            {
                TracingRibbontab.Visibility = System.Windows.Visibility.Visible;
                tabFixes.Visibility = System.Windows.Visibility.Visible;
                tabScheduling.Visibility = System.Windows.Visibility.Visible;
                Performance_trace.Visibility = System.Windows.Visibility.Visible;
                AddtoScheduling.Visibility = System.Windows.Visibility.Visible;
                tabEmail.Visibility = System.Windows.Visibility.Visible;
                tabSettings.Visibility = System.Windows.Visibility.Visible;
                Settings.Visibility = System.Windows.Visibility.Visible;
            }

        }

        void win_SourceInitialized(object sender, EventArgs e)
        {
            try
            {
                System.IntPtr handle = (new WinInterop.WindowInteropHelper(this)).Handle;
                WinInterop.HwndSource.FromHwnd(handle).AddHook(new WinInterop.HwndSourceHook(WindowProc));
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }

        }

        void TraceStatusCheckTimerUpdateStatus(object sender, EventArgs e)
        {

            foreach (DatabaseTrace databasetrace in LstBox_tracing.Items)
            {
                ListBoxItem ListBoxItem = (ListBoxItem)(this.LstBox_tracing.ItemContainerGenerator.ContainerFromItem(databasetrace) as ListBoxItem);

                ContentPresenter ContentPresenter = FindVisualChild<ContentPresenter>(ListBoxItem);
                DataTemplate TracingDataTemplate = ContentPresenter.ContentTemplate;
                Expander TracingExpander = (Expander)TracingDataTemplate.FindName("Tracing_expander", ContentPresenter);

                System.Windows.Controls.ComboBox InstanceComboBox = (System.Windows.Controls.ComboBox)TracingExpander.FindName("combobox_tracing_instance");
                if (InstanceComboBox == null) return;

                System.Windows.Controls.ComboBox DatabaseComboBox = (System.Windows.Controls.ComboBox)TracingExpander.FindName("combobox_tracing_database");
                if (DatabaseComboBox == null) return;

                DatePicker startDate = (DatePicker)TracingDataTemplate.FindName("calendar_Startdate", ContentPresenter);
                if (startDate == null) return;

                DatePicker endDate = (DatePicker)TracingDataTemplate.FindName("calendar_Enddate", ContentPresenter);
                if (startDate == null) return;

                System.Windows.Controls.ComboBox starttime_hourComboBox = (System.Windows.Controls.ComboBox)TracingDataTemplate.FindName("Time_StartTime_Hour", ContentPresenter);
                if (starttime_hourComboBox == null) return;

                System.Windows.Controls.ComboBox endtime_minComboBox = (System.Windows.Controls.ComboBox)TracingDataTemplate.FindName("Time_StartTime_Min", ContentPresenter);
                if (endtime_minComboBox == null) return;

                System.Windows.Controls.ComboBox Time_EndTime_Hour = (System.Windows.Controls.ComboBox)TracingDataTemplate.FindName("Time_EndTime_Hour", ContentPresenter);
                if (Time_EndTime_Hour == null) return;

                System.Windows.Controls.ComboBox Time_EndTime_Min = (System.Windows.Controls.ComboBox)TracingDataTemplate.FindName("Time_EndTime_Min", ContentPresenter);
                if (Time_EndTime_Min == null) return;

                Button scheduleButton = (Button)TracingDataTemplate.FindName("sceduleButton", ContentPresenter);
                if (scheduleButton == null) return;

                TracingExpander.IsExpanded = false;

                if (databasetrace.State == DatabaseTraceState.Finished)
                {
                    string imageUri = "pack://application:,,,/SQLServerOptimizer.PL.WPF;component/Images/reports.png";
                    scheduleButton.IsEnabled = true;
                    scheduleButton.ToolTip = "Show tracing Reports";
                    Image _imgrestor_down = (Image)TracingDataTemplate.FindName("image_capturetrace", ContentPresenter);
                    if (_imgrestor_down == null) return;
                    _imgrestor_down.Source = new ImageSourceConverter().ConvertFromString(imageUri) as ImageSource;

                    Label labelTracingUpdate = (Label)scheduleButton.FindName("traceProgressupdate");
                    if (labelTracingUpdate == null)
                        return;

                    labelTracingUpdate.Content = "Trace is Finished.";
                }
                else if (databasetrace.State == DatabaseTraceState.IsRunning)
                {

                    string imageUri = "pack://application:,,,/SQLServerOptimizer.PL.WPF;component/Images/tracerunning.png";
                    scheduleButton.IsEnabled = false;
                    scheduleButton.ToolTip = "Trace is Running";

                    Image _imgrestor_down = (Image)scheduleButton.FindName("image_capturetrace");
                    if (_imgrestor_down == null) return;

                    _imgrestor_down.Source = new ImageSourceConverter().ConvertFromString(imageUri) as ImageSource;

                    Label labelTracingUpdate = (Label)scheduleButton.FindName("traceProgressupdate");
                    if (labelTracingUpdate == null)
                        return;


                    labelTracingUpdate.Content = "Trace is Running.";
                }
                else if (databasetrace.State == DatabaseTraceState.Scheduled)
                {

                    string imageUri = "pack://application:,,,/SQLServerOptimizer.PL.WPF;component/Images/tracerunning.png";
                    scheduleButton.IsEnabled = false;
                    scheduleButton.ToolTip = "Trace is Scheduled";

                    Image _imgrestor_down = (Image)scheduleButton.FindName("image_capturetrace");
                    if (_imgrestor_down == null) return;

                    _imgrestor_down.Source = new ImageSourceConverter().ConvertFromString(imageUri) as ImageSource;

                    Label labelTracingUpdate = (Label)scheduleButton.FindName("traceProgressupdate");
                    if (labelTracingUpdate == null)
                        return;


                    labelTracingUpdate.Content = "Trace is Scheduled.";
                }
                else if (databasetrace.State == DatabaseTraceState.New)
                {

                    string imageUri = "pack://application:,,,/SQLServerOptimizer.PL.WPF;component/Images/reports.png";
                    scheduleButton.ToolTip = "New Trace";

                    Image _imgrestor_down = (Image)scheduleButton.FindName("image_capturetrace");
                    if (_imgrestor_down == null) return;

                    _imgrestor_down.Source = new ImageSourceConverter().ConvertFromString(imageUri) as ImageSource;

                    Label labelTracingUpdate = (Label)scheduleButton.FindName("traceProgressupdate");
                    if (labelTracingUpdate == null)
                        return;


                    labelTracingUpdate.Content = "New Trace.";
                }

            }




        }


        private void DragableGridMouseDown(object sender, MouseButtonEventArgs e)
        {
            try
            {
                if (e.LeftButton == MouseButtonState.Pressed)
                    DragMove();

            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }

        }


        private void Add(object sender, RoutedEventArgs e)
        {
            if (!isRunning)
            {
                try
                {

                    SQLServerInstance SqlServerInstance = new SQLServerInstance();
                    SqlServerInstance.InstanceName = "<New Instance>";
                    SqlServerInstance.Port = "1433";
                    SqlServerInstance.Authentication = "Windows Authentication";
                    SqlServerInstance.Login = "";

                    SqlServerInstance.Password = "";

                    this.SQLServerInstances.Insert(SQLServerInstances.Count, SqlServerInstance);

                    System.Threading.ThreadPool.QueueUserWorkItem(
                          (a) =>
                          {
                              System.Threading.Thread.Sleep(100);
                              this.Dispatcher.Invoke(
                              new Action(() =>
                              {

                                  ListBoxItem ListBoxItem = (ListBoxItem)(Instance_ListBox.ItemContainerGenerator.ContainerFromItem(SqlServerInstance) as ListBoxItem);
                                  if (ListBoxItem == null)
                                      return;

                                  ContentPresenter ContentPresenter = FindVisualChild<ContentPresenter>(ListBoxItem);

                                  DataTemplate InstanceDataTemplate = ContentPresenter.ContentTemplate;

                                  Expander InstanceExpander = (Expander)InstanceDataTemplate.FindName("Instance_expander", ContentPresenter);

                                  System.Windows.Controls.TextBox InstanceTextBox = (System.Windows.Controls.TextBox)InstanceExpander.FindName("txtInstance");
                                  if (InstanceTextBox == null) return;

                                  InstanceExpander.IsExpanded = true;
                                  InstanceTextBox.Focus();
                              }));
                          }
                          );


                }
                catch (Exception ex)
                {
                    Logger.Error(ex);
                }
            }

        }
        private void btnRemoveAll(object sender, RoutedEventArgs e)
        {
            if (isRunning == true)
            {

                MessageBox.Show("Please wait until the SQL Optimizer has finished collecting data before removing the selected instance.", "Process currently running!");

            }
            else
            {
                try
                {
                    this.SQLServerInstances.Clear();

                    var bc = new BrushConverter();
                    statusBar.Foreground = (Brush)bc.ConvertFrom("#003366");
                    statusText.Text = "Ready.";

                }
                catch (Exception ex)
                {
                    Logger.Error(ex);
                }
                finally
                {

                    isRunning = false;

                }
            }
        }
        private void OnClear(object sender, RoutedEventArgs e)
        {
            if (isRunning == true)
            {

                MessageBox.Show("Please wait until the SQL Optimizer has finished collecting data before removing the selected instance.", "Process currently running!");

            }
            else
            {
                try
                {

                    while (Instance_ListBox.SelectedItems.Count > 0)
                    {

                        this.SQLServerInstances.Remove((SQLServerInstance)Instance_ListBox.SelectedItem);


                    }

                    var bc = new BrushConverter();
                    statusBar.Foreground = (Brush)bc.ConvertFrom("#003366");
                    statusText.Text = "Ready.";

                }
                catch (Exception ex)
                {
                    Logger.Error(ex);
                }
                finally
                {



                }
            }
        }
        private void ComboSort_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {



        }
        private void tabMain_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {

        }

        private void Instance_ListBox_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {

        }

        private void cbo_Severity_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {

        }

        private childItem FindVisualChild<childItem>(DependencyObject obj)
                where childItem : DependencyObject
        {
            try
            {

                for (int i = 0; i < VisualTreeHelper.GetChildrenCount(obj); i++)
                {
                    DependencyObject child = VisualTreeHelper.GetChild(obj, i);
                    if (child != null && child is childItem)
                        return (childItem)child;
                    else
                    {
                        childItem childOfChild = FindVisualChild<childItem>(child);
                        if (childOfChild != null)
                            return childOfChild;
                    }
                }

            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }

            return null;

        }
        public void DoProgress(SQLServerInstance instance, double status, string task)
        {
            try
            {

                ListBoxItem ListBoxItem = (ListBoxItem)(Instance_ListBox.ItemContainerGenerator.ContainerFromItem(instance) as ListBoxItem);
                if (ListBoxItem == null)
                    return;

                ContentPresenter ContentPresenter = FindVisualChild<ContentPresenter>(ListBoxItem);
                DataTemplate InstanceDataTemplate = ContentPresenter.ContentTemplate;

                TextBox InstanceTextBox = (TextBox)InstanceDataTemplate.FindName("txtInstance", ContentPresenter);
                string i = instance.InstanceName;

                ProgressBar InProgressBar = (ProgressBar)InstanceDataTemplate.FindName("Pbar", ContentPresenter);
                TextBlock InProgressText = (TextBlock)InstanceDataTemplate.FindName("progtext", ContentPresenter);
                if ((InProgressBar.Value != 100) && (InstanceTextBox.Text == instance.InstanceName))
                {
                    var bc = new BrushConverter();
                    if (status == 100)
                    {

                        System.Threading.ThreadPool.QueueUserWorkItem(
                               (a) =>
                               {
                                   System.Threading.Thread.Sleep(100);
                                   this.Dispatcher.Invoke(
                                   new Action(() =>
                                   {


                                       InProgressBar.Visibility = Visibility;
                                       InProgressBar.Value = status;
                                       StringBuilder finish_builder = new StringBuilder();
                                       finish_builder.Append(task);
                                       finish_builder.Append(" : ");
                                       finish_builder.Append(String.Format("Optimization status {0}%", status));


                                       InProgressText.Foreground = (Brush)bc.ConvertFrom("#006600");
                                       InProgressText.Text = finish_builder.ToString();



                                   }));
                               }
                               );




                    }
                    else
                    {
                        System.Threading.ThreadPool.QueueUserWorkItem(
                               (a) =>
                               {
                                   System.Threading.Thread.Sleep(100);
                                   this.Dispatcher.Invoke(
                                   new Action(() =>
                                   {
                                       InProgressBar.Visibility = Visibility;
                                       InProgressBar.Value = status;
                                       StringBuilder builder = new StringBuilder();
                                       builder.Append(task);
                                       builder.Append(" : ");
                                       builder.Append(String.Format("Optimization status {0}%", status));

                                       var bc1 = new BrushConverter();
                                       InProgressText.Foreground = (Brush)bc1.ConvertFrom("#000033");
                                       InProgressText.Text = builder.ToString();

                                   }));
                               }
                               );

                    }
                }


            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
        }

        private void ResetUI()
        {
            try
            {
                foreach (object item in Instance_ListBox.SelectedItems)
                {


                    ListBoxItem ListBoxItem = (ListBoxItem)(Instance_ListBox.ItemContainerGenerator.ContainerFromItem(Instance_ListBox.SelectedItems) as ListBoxItem);
                    if (ListBoxItem == null)
                        return;

                    ContentPresenter ContentPresenter = FindVisualChild<ContentPresenter>(ListBoxItem);
                    DataTemplate InstanceDataTemplate = ContentPresenter.ContentTemplate;


                    ProgressBar InProgressBar = (ProgressBar)InstanceDataTemplate.FindName("Pbar", ContentPresenter);
                    TextBlock InProgressText = (TextBlock)InstanceDataTemplate.FindName("progtext", ContentPresenter);

                    if ((InProgressBar.Value == 100))
                    {
                        InProgressBar.Value = 0;
                        InProgressText.Text = "";
                    }
                    statusText.Text = "Ready.";
                }

            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
        }
        private void btnStartCheck_Click(object sender, RoutedEventArgs e)
        {
            if (SQLServerInstances.Count() != 0)
            {

                //btnStartCheck.IsEnabled = false;
                InstancesCount = SQLServerInstances.Count();
                try
                {

                    if (isRunning)
                    {
                        datacollectionWorker.Cancel();


                        foreach (SQLServerInstance instance in SQLServerInstances)
                        {

                            ListBoxItem ListBoxItem = (ListBoxItem)(Instance_ListBox.ItemContainerGenerator.ContainerFromItem(instance) as ListBoxItem);
                            if (ListBoxItem == null)
                                return;

                            ContentPresenter ContentPresenter = FindVisualChild<ContentPresenter>(ListBoxItem);
                            DataTemplate InstanceDataTemplate = ContentPresenter.ContentTemplate;

                            Expander expander = (Expander)InstanceDataTemplate.FindName("Instance_expander", ContentPresenter);
                            ProgressBar InProgressBar = (ProgressBar)InstanceDataTemplate.FindName("Pbar", ContentPresenter);
                            TextBlock InProgressText = (TextBlock)InstanceDataTemplate.FindName("progtext", ContentPresenter);

                            expander.IsExpanded = false;

                            if (InProgressBar.Value >= 0)
                            {

                                InProgressBar.Value = 0;
                                InProgressText.Text = "";
                            }

                            //    System.Threading.ThreadPool.QueueUserWorkItem(
                            //            (a) =>
                            //            {
                            //                System.Threading.Thread.Sleep(100);
                            //                this.Dispatcher.Invoke(
                            //                new Action(() =>
                            //                {
                            //                    expander.IsExpanded = false;

                            //                    if (InProgressBar.Value >= 0)
                            //                    {

                            //                        InProgressBar.Value = 0;
                            //                        InProgressText.Text = "";
                            //                    }

                            //                }));
                            //            }
                            //            );


                        }

                        string imageUri = "pack://application:,,,/SQLServerOptimizer.PL.WPF;component/Images/start.png";
                        var button = sender as Button;
                        button.ToolTip = "Start Scan";

                        btnStartCheck.IsEnabled = true;
                        Image _imgrestor_down = (Image)button.FindName("image_start");
                        if (_imgrestor_down == null)
                            return;
                        _imgrestor_down.Source = new ImageSourceConverter().ConvertFromString(imageUri) as ImageSource;

                        this.Cursor = Cursors.Arrow;
                    }
                    else
                    {

                        foreach (SQLServerInstance instance in SQLServerInstances)
                        {

                            ListBoxItem ListBoxItem = (ListBoxItem)(Instance_ListBox.ItemContainerGenerator.ContainerFromItem(instance) as ListBoxItem);
                            if (ListBoxItem == null)
                                return;

                            ContentPresenter ContentPresenter = FindVisualChild<ContentPresenter>(ListBoxItem);
                            DataTemplate InstanceDataTemplate = ContentPresenter.ContentTemplate;

                            Expander expander = (Expander)InstanceDataTemplate.FindName("Instance_expander", ContentPresenter);
                            ProgressBar InProgressBar = (ProgressBar)InstanceDataTemplate.FindName("Pbar", ContentPresenter);
                            TextBlock InProgressText = (TextBlock)InstanceDataTemplate.FindName("progtext", ContentPresenter);

                            expander.IsExpanded = false;

                            if (InProgressBar.Value == 100)
                            {

                                InProgressBar.Value = 0;
                                InProgressText.Text = "";
                            }



                            //System.Threading.ThreadPool.QueueUserWorkItem(
                            //        (a) =>
                            //        {
                            //            System.Threading.Thread.Sleep(100);
                            //            this.Dispatcher.Invoke(
                            //            new Action(() =>
                            //            {
                            //                expander.IsExpanded = false;

                            //                if (InProgressBar.Value == 100)
                            //                {

                            //                    InProgressBar.Value = 0;
                            //                    InProgressText.Text = "";
                            //                }

                            //            }));
                            //        }
                            //        );


                        }


                        var button = sender as Button;
                        statusText.Text = "Starting Data Collection...";
                        this.Cursor = Cursors.AppStarting;
                        string imageUri = "pack://application:,,,/SQLServerOptimizer.PL.WPF;component/Images/stop.png";
                        button.ToolTip = "Stop Scan";
                        Image _imgrestor_down = (Image)button.Template.FindName("image_start", button);
                        if (_imgrestor_down == null)
                            return;
                        _imgrestor_down.Source = new ImageSourceConverter().ConvertFromString(imageUri) as ImageSource;

                        // Clear the Connected Instances before Calling the Connectivity Status Worker Class
                        this.ConnectedSQLServerInstances.Clear();
                        // Start Connectivity Status Worker.
                        isRunning = true;
                        _statusworker.Start();

                        // }
                        // startPressed = false;
                    }
                }

                catch (Exception ex)
                {
                    Logger.Error(ex);
                    btnStartCheck.IsEnabled = true;

                }
            }
        }
        private void _statusworker_StatusCheckProccessCompleted()
        {

            try
            {


                var button = this.btnStartCheck as Button;


                if (this.ConnectedSQLServerInstances.Count > 0)
                {
                    datacollectionWorker.Start();

                }
                else
                {
                    string imageUri = "pack://application:,,,/SQLServerOptimizer.PL.WPF;component/Images/start.png";

                    button.ToolTip = "Start Scan";

                    btnStartCheck.IsEnabled = true;
                    Image _imgrestor_down = (Image)button.Template.FindName("image_start", button);
                    if (_imgrestor_down == null)
                        return;
                    _imgrestor_down.Source = new ImageSourceConverter().ConvertFromString(imageUri) as ImageSource;

                    var bc = new BrushConverter();
                    statusBar.Foreground = (Brush)bc.ConvertFrom("#FF0000");
                    statusText.Text = "Please add a SQL Server Instance.";
                    this.Cursor = Cursors.Arrow;
                    //isRunning = !isRunning;
                }

            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }

            //isRunning = !isRunning;





        }
        private void datacollectionWorker_Completed(object sender, EventArgs e)
        {
            try
            {
                var bc = new BrushConverter();
                statusBar.Foreground = (Brush)bc.ConvertFrom("#003366");
                statusText.Text = "Preparing to Optimize...";
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
        }
        private void datacollectionWorker_Cancelled(object sender, EventArgs e)
        {
            // ResetUI();
            statusText.Text = "Work cancelled.";
        }
        private void datacollectionWorker_Failed(object sender, EventArgs e)
        {
            //ResetUI();
            statusText.Text = "Work FAILED!";
        }

        public void datacollectionWorker_ProccessCompleted()
        {
            try
            {

                string imageUri = "pack://application:,,,/SQLServerOptimizer.PL.WPF;component/Images/start.png";
                var button = this.btnStartCheck as Button;
                button.ToolTip = "Start Scan";
                Image _imgrestor_down = (Image)button.Template.FindName("image_start", button);
                if (_imgrestor_down == null)
                    return;

                _imgrestor_down.Source = new ImageSourceConverter().ConvertFromString(imageUri) as ImageSource;
                var bc = new BrushConverter();
                statusBar.Foreground = (Brush)bc.ConvertFrom("#003366");
                statusText.Text = "SQL Server Optimization Completed Successfully...";
                OptimizationFinishCount += 1;
                // showIssues();

                Storyboard translationAnimationStoryboard = (Storyboard)this.Resources["TabStoryboardResource"];
                translationAnimationStoryboard.Seek(this, new TimeSpan(0, 0, 0), TimeSeekOrigin.BeginTime);
                translationAnimationStoryboard.Begin(this, true);

                tabOptimize.SelectedIndex = 1;
                FillSQLServicesComboboxA();
                IssueReport.RefreshReport();
                this.OverviewReports.UserControl_Loaded(null, null);
                this.Cursor = Cursors.Arrow;


            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
            finally
            {
                isRunning = false;
                btnStartCheck.IsEnabled = true;


            }

        }

        public void datacollectionWorker_ProgressUpdate(SQLServerInstance Instance, string Task, decimal Status)
        {
            try
            {
                var bc = new BrushConverter();
                statusBar.Foreground = (Brush)bc.ConvertFrom("#003366");

                if (statusText.Text == "Ready")
                {
                    this.statusText.Text = "Collecting Data from SQL Server " + Instance.InstanceName;

                    DoProgress(Instance, (double)Status, Task);
                }


                else
                {
                    this.statusText.Text = "";
                    this.statusText.Text = "Collecting Data from SQL Server " + Instance.InstanceName;
                    DoProgress(Instance, (double)Status, Task);
                }

            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }

        }

        public void _statusworker_InstanceStatusResult(SQLServerInstance Instance, bool result)
        {


            try
            {
                if (result == true)
                {
                    this.ConnectedSQLServerInstances.Add(Instance);
                    System.Threading.ThreadPool.QueueUserWorkItem(
                                   (a) =>
                                   {

                                       this.Dispatcher.Invoke(
                                       new Action(() =>
                                       {



                                           ListBoxItem ListBoxItem = (ListBoxItem)(Instance_ListBox.ItemContainerGenerator.ContainerFromItem(Instance) as ListBoxItem);
                                           if (ListBoxItem == null)
                                               return;
                                           ContentPresenter ContentPresenter = FindVisualChild<ContentPresenter>(ListBoxItem);
                                           DataTemplate InstanceDataTemplate = ContentPresenter.ContentTemplate;
                                           Image InstanceStatusImage = (Image)InstanceDataTemplate.FindName("InstanceStatusIMG", ContentPresenter);
                                           TextBlock Progresstext = (TextBlock)InstanceDataTemplate.FindName("progtext", ContentPresenter);
                                           string imageUri = "pack://application:,,,/SQLServerOptimizer.PL.WPF;component/Images/connectionsuccess.png";
                                           InstanceStatusImage.Source = new ImageSourceConverter().ConvertFromString(imageUri) as ImageSource;
                                           InstanceStatusImage.Visibility = Visibility.Visible;
                                           var bc = new BrushConverter();
                                           Progresstext.Foreground = (Brush)bc.ConvertFrom("#008000");
                                           Progresstext.Text = "Online";


                                       }));
                                   }
                                    );
                }
                else
                {
                    System.Threading.ThreadPool.QueueUserWorkItem(
                                   (a) =>
                                   {
                                       this.Dispatcher.Invoke(
                                       new Action(() =>
                                       {

                                           ListBoxItem ListBoxItem = (ListBoxItem)(Instance_ListBox.ItemContainerGenerator.ContainerFromItem(Instance) as ListBoxItem);
                                           if (ListBoxItem == null)
                                               return;
                                           ContentPresenter ContentPresenter = FindVisualChild<ContentPresenter>(ListBoxItem);
                                           DataTemplate InstanceDataTemplate = ContentPresenter.ContentTemplate;
                                           Image InstanceStatusImage = (Image)InstanceDataTemplate.FindName("InstanceStatusIMG", ContentPresenter);
                                           TextBlock Progresstext = (TextBlock)InstanceDataTemplate.FindName("progtext", ContentPresenter);
                                           string imageUri = "pack://application:,,,/SQLServerOptimizer.PL.WPF;component/Images/connectionerror.png";
                                           InstanceStatusImage.Source = new ImageSourceConverter().ConvertFromString(imageUri) as ImageSource;
                                           InstanceStatusImage.Visibility = Visibility.Visible;
                                           var bc = new BrushConverter();
                                           Progresstext.Foreground = (Brush)bc.ConvertFrom("#FF0000");
                                           Progresstext.Text = "Offline Please verify the instance settings.";
                                           OptimizationFinishCount += 1;
                                           if (OptimizationFinishCount == InstancesCount)
                                           {
                                               isRunning = false;
                                               OptimizationFinishCount = 0;
                                           }
                                       }));
                                   }
                                    );

                }



            }
            catch (Exception ex)
            {
                Logger.Error(ex);

            }
        }


        public bool _statusworker_StatusUpdate(string Instance, string Status)
        {
            if (Status == "Online")
            {
                this.stateOnline = true;
                this.INt = Instance;
                return true;
            }
            else { return false; }
        }

        public void HelpExecuted(object sender, ExecutedRoutedEventArgs e)
        {

            System.Diagnostics.Process.Start("http://www.sqlserveroptimizer.com/support/");

        }

        public void HelpCanExecute(object sender, CanExecuteRoutedEventArgs e)
        {

            e.CanExecute = true;
        }

        private void btnSelectAll_Click(object sender, RoutedEventArgs e)
        {
            Instance_ListBox.SelectAll();
        }

        private void btnUnSelectall_Click(object sender, RoutedEventArgs e)
        {
            Instance_ListBox.UnselectAll();

        }


        private void btnSaveInstances_Click(object sender, RoutedEventArgs e)
        {

            try
            {

                Microsoft.Win32.SaveFileDialog dlg = new Microsoft.Win32.SaveFileDialog();

                // Set filter for file extension and default file extension
                dlg.DefaultExt = ".txt";
                dlg.Filter = "Text documents (.txt)|*.txt";

                // Display OpenFileDialog by calling ShowDialog method
                Nullable<bool> result = dlg.ShowDialog();

                if (result == true)
                {
                    try
                    {
                        String instanceLine = "";
                        using (StreamWriter writer = new StreamWriter(dlg.FileName))
                        {
                            foreach (SQLServerInstance instance in SQLServerInstances)
                            {
                                instanceLine = instance.InstanceName + "," + instance.Port + "," + instance.Authentication + "," + instance.Login + "," + instance.Password;
                                writer.WriteLine(instanceLine);
                            }

                        }
                    }
                    catch (IOException ex)
                    {
                        Logger.Error(ex);
                    }
                    finally
                    {
                        this.Cursor = Cursors.Arrow;
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
        }

        private void btnImport_instance(object sender, RoutedEventArgs e)
        {
            try
            {

                Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog();

                // Set filter for file extension and default file extension
                dlg.DefaultExt = ".txt";
                dlg.Filter = "Text documents (.txt)|*.txt";

                // Display OpenFileDialog by calling ShowDialog method
                Nullable<bool> result = dlg.ShowDialog();

                if (result == true)
                {


                    // Open text document and assume that user has entered all the 
                    // Instance properties seperated by comma's eg  (local)\express,1433,Windows Authentication,UserName,Password
                    string filename = dlg.FileName;
                    string line;
                    List<string> ImportedInstancelist = new List<string>();
                    char[] delimiters = { ',', ';' };
                    try
                    {

                        this.Cursor = Cursors.Wait;
                        this.Cursor = Cursors.AppStarting;



                        using (StreamReader reader = new StreamReader(filename))
                        {
                            while ((line = reader.ReadLine()) != null)
                            {
                                ImportedInstancelist = new List<string>();

                                ImportedInstancelist.AddRange(line.Split(delimiters, 5)); // Add to list.
                                if (ImportedInstancelist[0] != null)
                                {
                                    SQLServerInstance SqlServerInstance = new SQLServerInstance();
                                    SqlServerInstance.InstanceName = ImportedInstancelist[0].Trim();
                                    SqlServerInstance.Port = ImportedInstancelist[1].Trim();
                                    SqlServerInstance.Authentication = ImportedInstancelist[2].Trim();
                                    if (ImportedInstancelist.Count >= 5)
                                    {
                                        SqlServerInstance.Login = ImportedInstancelist[3].Trim();
                                        SqlServerInstance.Password = ImportedInstancelist[4].Trim();
                                    }
                                    this.SQLServerInstances.Insert(SQLServerInstances.Count, SqlServerInstance);


                                }
                            }

                        }

                    }
                    catch (IOException ex)
                    {
                        Logger.Error(ex);
                    }
                    finally
                    {

                        this.Cursor = Cursors.Arrow;

                    }

                }

            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
        }

        private void btnAdvanceOptions_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                OptionDialog dialog = new OptionDialog(this);
                if (dialog.ShowDialog() == true)
                {

                }

            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
        }


        private void combobox_servicefilter_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            try
            {
                int serviceId = -1;

                if (this.combobox_servicefilterA.SelectedIndex != -1)
                {
                    try
                    {
                        this.IssueReport.instance = (((System.Windows.Controls.Primitives.Selector)(sender)).SelectedItem as Service);

                        serviceId = this.IssueReport.instance.ServiceID;

                        //if (this.IssueReport.instance != null)
                        //{
                        //    var qry = from db in dc.Databases
                        //              where db.ServiceID == this.IssueReport.instance.ServiceID
                        //              select db;

                        //    List<Database> databases = qry.ToList<Database>();
                        //    Database dummy = new Database { DatabaseID = -1, DatabaseName = "All" };

                        //    databases.Insert(0, dummy);

                        //    this.combobox_databasefilter.ItemsSource = databases;
                        //    this.combobox_databasefilter.DisplayMemberPath = "DatabaseName";

                        //}
                    }
                    catch (Exception ex)
                    {
                        Logger.Error(ex);
                    }
                }
                this.IssueReport.combobox_servicefilter.SelectedIndex = combobox_servicefilterA.SelectedIndex;
                this.IssueReport.RefreshReport();
                this.combobox_databasefilterA.SelectedIndex = 0;

            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
        }
        public void FillSQLServicesCombobox()
        {
            try
            {
                string conn = System.Configuration.ConfigurationManager.AppSettings.Get("ConnectionStringSSHCDatabase");
                CheckInstanceStatusWorker checkinstanceStatusworker = new CheckInstanceStatusWorker(this, conn);

                //  checkinstanceStatusworker.AccessiblityResult += new InstanceStatusResult(checkinstanceStatusworker_AccessiblityResult);
                //  checkinstanceStatusworker.Start();
            }
            catch (Exception sql)
            {
                Logger.Error(sql);
            }
        }

        //private void checkinstanceStatusworker_AccessiblityResult(bool accessible)
        //{
        //    try
        //    {

        //        if (accessible)
        //        {
        //            dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings.Get("ConnectionStringSSHCDatabase"));

        //            var qry = from i in dc.Services
        //                      where i.ServiceTypeID == 1
        //                      select i;
        //            this.IssueReport.SQLServerInstances = qry.ToList<Service>();
        //            Server dummy = new Server { Name = "All" };
        //            Service dummyService = new Service { ServiceID = -1, Server = dummy, Name = "" };

        //            this.IssueReport.SQLServerInstances.Insert(0, dummyService);

        //            this.combobox_servicefilter.ItemsSource = this.IssueReport.SQLServerInstances;
        //            this.combobox_servicefilter.DisplayMemberPath = "InstanceName";
        //            this.combobox_servicefilter.SelectedValuePath = "ServiceID";

        //            this.combobox_servicefilter.SelectedIndex = 0;

        //            List<Database> databases = new List<Database>();
        //            Database dummyDatabase = new Database { DatabaseID = -1, DatabaseName = "All" };

        //            databases.Insert(0, dummyDatabase);


        //            this.combobox_databasefilter.ItemsSource = databases;
        //            this.combobox_databasefilter.DisplayMemberPath = "DatabaseName";

        //            this.combobox_databasefilter.SelectedIndex = 0;

        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        Logger.Error(ex);
        //    }
        //}

        private void combobox_Expand_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            if (isloaded)
            {
                this.IssueReport.combobox_Expand.SelectedIndex = ((System.Windows.Controls.Primitives.Selector)(sender)).SelectedIndex;
                this.IssueReport.RefreshReport();
            }

        }
        private void combobox_databasefilter_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {

            try
            {
                this.IssueReport.combobox_databasefilter.SelectedIndex = ((System.Windows.Controls.Primitives.Selector)(sender)).SelectedIndex;
                this.IssueReport.RefreshReport();
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
        }

        private void btnLoad_Click(object sender, RoutedEventArgs e)
        {
            this.IssueReport.RefreshReport();
        }


        private String GetDatabaseName(int? targetId, int scopeId)
        {
            if (targetId.HasValue)
            {
                String databaseName = "";
                //Server
                if (scopeId == 1)
                {
                }
                //Service
                else if (scopeId == 2)
                {
                }
                //Database
                else if (scopeId == 3)
                {
                    Database database = dc.Databases.FirstOrDefault(d => d.DatabaseID == targetId.Value);
                    if (database != null)
                    {
                        databaseName = database.DatabaseName;
                    }
                }
                // File
                else if (scopeId == 4)
                {
                    DatabaseFile dbfile = dc.DatabaseFiles.FirstOrDefault(df => df.DatabaseFileID == targetId.Value);
                    if (dbfile != null)
                    {
                        databaseName = dbfile.Database.DatabaseName;
                    }
                }
                //Table
                else if (scopeId == 5)
                {
                    SQLServerHealthCheck.Model.Table table = dc.Tables.FirstOrDefault(t => t.TableID == targetId.Value);
                    if (table != null)
                    {
                        databaseName = table.Database.DatabaseName;
                    }
                }
                // Index
                else if (scopeId == 6)
                {
                    Index index = dc.Indexes.FirstOrDefault(i => i.IndexID == targetId.Value);
                    if (index != null)
                    {
                        databaseName = index.Table.Database.DatabaseName;
                    }
                }

                 // User
                else if (scopeId == 9)
                {
                    DatabaseUser user = dc.DatabaseUsers.FirstOrDefault(u => u.DatabaseUserId == targetId.Value);
                    if (user != null)
                    {
                        databaseName = user.Database.DatabaseName;
                    }
                }
                return databaseName;
            }
            else
            {
                return "";
            }
        }

        private String GetInstanceName(int? targetId, int scopeId)
        {
            if (targetId.HasValue)
            {
                String instanceName = "";
                //Server
                if (scopeId == 1)
                {

                }
                //Service
                else if (scopeId == 2)
                {
                    Service service = dc.Services.FirstOrDefault(s => s.ServiceID == targetId.Value);
                    if (service != null)
                    {
                        instanceName = service.InstanceName;
                    }
                }
                //Database
                else if (scopeId == 3)
                {
                    Database database = dc.Databases.FirstOrDefault(d => d.DatabaseID == targetId.Value);
                    if (database != null)
                    {
                        instanceName = database.Service.InstanceName;
                    }

                }
                else if (scopeId == 4)
                {
                    DatabaseFile dbfile = dc.DatabaseFiles.FirstOrDefault(df => df.DatabaseFileID == targetId.Value);
                    if (dbfile != null)
                    {
                        instanceName = dbfile.Database.Service.InstanceName;
                    }

                }
                //Table
                else if (scopeId == 5)
                {
                    SQLServerHealthCheck.Model.Table table = dc.Tables.FirstOrDefault(t => t.TableID == targetId.Value);
                    if (table != null)
                    {
                        instanceName = table.Database.Service.InstanceName;
                    }

                }
                else if (scopeId == 6)
                {
                    Index index = dc.Indexes.FirstOrDefault(i => i.IndexID == targetId.Value);
                    if (index != null)
                    {
                        instanceName = index.Table.Database.Service.InstanceName;
                    }
                }
                // User
                else if (scopeId == 9)
                {
                    DatabaseUser user = dc.DatabaseUsers.FirstOrDefault(u => u.DatabaseUserId == targetId.Value);
                    if (user != null)
                    {
                        instanceName = user.Database.Service.InstanceName;
                    }
                }

                return instanceName;
            }
            else
            {
                return "";
            }
        }

        private String GetServerName(int? targetId, int scopeId)
        {
            if (targetId.HasValue)
            {
                String serverName = "";
                //Server
                if (scopeId == 1)
                {
                    Server server = dc.Servers.FirstOrDefault(s => s.ServerID == targetId.Value);
                    if (server != null)
                    {
                        serverName = server.Name;
                    }
                }
                //Service
                else if (scopeId == 2)
                {
                    Service service = dc.Services.FirstOrDefault(s => s.ServiceID == targetId.Value);
                    if (service != null)
                    {
                        serverName = service.Server.Name;
                    }
                }
                //Database
                else if (scopeId == 3)
                {
                    Database database = dc.Databases.FirstOrDefault(d => d.DatabaseID == targetId.Value);
                    if (database != null)
                    {
                        serverName = database.Service.Server.Name;
                    }

                }
                //Table
                else if (scopeId == 5)
                {
                    SQLServerHealthCheck.Model.Table table = dc.Tables.FirstOrDefault(t => t.TableID == targetId.Value);
                    if (table != null)
                    {
                        serverName = table.Database.Service.Server.Name;
                    }

                }
                else if (scopeId == 6)
                {
                    Index index = dc.Indexes.FirstOrDefault(i => i.IndexID == targetId.Value);
                    if (index != null)
                    {
                        serverName = index.Table.Database.Service.Server.Name;
                    }
                }
                else if (scopeId == 9)
                {
                    DatabaseUser user = dc.DatabaseUsers.FirstOrDefault(u => u.DatabaseUserId == targetId.Value);
                    if (user != null)
                    {
                        serverName = user.Database.Service.Server.Name;
                    }
                }
                return serverName;
            }
            else
            {
                return "";
            }
        }

        private void rvDatabaseandFiles_Load(object sender, EventArgs e)
        {
            throw new NotImplementedException();
        }


        private void btnClose_Click(object sender, System.Windows.RoutedEventArgs e)
        {
            Close();
        }

        private void btnMaximizeWindow_Click(object sender, System.Windows.RoutedEventArgs e)
        {

        }

        private void btnChangeView_Click(object sender, System.Windows.RoutedEventArgs e)
        {
            try
            {
                //This Button event changes the restore down and maximize image in the buttons.

                if (this.WindowState == WindowState.Maximized)
                {
                    string imageUri = "pack://application:,,,/SQLServerOptimizer.PL.WPF;component/Images/maximize.png";

                    WindowState = WindowState.Normal;

                    var button = sender as Button;

                    button.ToolTip = "Maximize";

                    Image _imgrestor_down = (Image)button.FindName("restore_down");

                    _imgrestor_down.Source = new ImageSourceConverter().ConvertFromString(imageUri) as ImageSource;


                }
                else
                {
                    string imageUri2 = "pack://application:,,,/SQLServerOptimizer.PL.WPF;component/Images/change.png";

                    WindowState = WindowState.Maximized;

                    var button = sender as Button;

                    button.ToolTip = "Restore Down";

                    Image _imgrestor_down = (Image)button.FindName("restore_down");

                    _imgrestor_down.Source = new ImageSourceConverter().ConvertFromString(imageUri2) as ImageSource;



                }

            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
        }

        private void btnMinimizeWindow_Click(object sender, System.Windows.RoutedEventArgs e)
        {
            WindowState = WindowState.Minimized;
        }
        private static System.IntPtr WindowProc(
      System.IntPtr hwnd,
      int msg,
      System.IntPtr wParam,
      System.IntPtr lParam,
      ref bool handled)
        {

            switch (msg)
            {
                case 0x0024:
                    WmGetMinMaxInfo(hwnd, lParam);
                    handled = true;
                    break;
            }

            return (System.IntPtr)0;
        }

        private static void WmGetMinMaxInfo(System.IntPtr hwnd, System.IntPtr lParam)
        {
            try
            {
                MINMAXINFO mmi = (MINMAXINFO)Marshal.PtrToStructure(lParam, typeof(MINMAXINFO));

                // Adjust the maximized size and position to fit the work area of the correct monitor
                int MONITOR_DEFAULTTONEAREST = 0x00000002;
                System.IntPtr monitor = MonitorFromWindow(hwnd, MONITOR_DEFAULTTONEAREST);

                if (monitor != System.IntPtr.Zero)
                {

                    MONITORINFO monitorInfo = new MONITORINFO();
                    GetMonitorInfo(monitor, monitorInfo);
                    RECT rcWorkArea = monitorInfo.rcWork;
                    RECT rcMonitorArea = monitorInfo.rcMonitor;
                    mmi.ptMaxPosition.x = Math.Abs(rcWorkArea.left - rcMonitorArea.left);
                    mmi.ptMaxPosition.y = Math.Abs(rcWorkArea.top - rcMonitorArea.top);
                    mmi.ptMaxSize.x = Math.Abs(rcWorkArea.right - rcWorkArea.left);
                    mmi.ptMaxSize.y = Math.Abs(rcWorkArea.bottom - rcWorkArea.top);
                }

                Marshal.StructureToPtr(mmi, lParam, true);

            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
        }


        /// <summary>
        /// POINT aka POINTAPI
        /// </summary>
        [StructLayout(LayoutKind.Sequential)]
        public struct POINT
        {
            /// <summary>
            /// x coordinate of point.
            /// </summary>
            public int x;
            /// <summary>
            /// y coordinate of point.
            /// </summary>
            public int y;

            /// <summary>
            /// Construct a point of coordinates (x,y).
            /// </summary>
            public POINT(int x, int y)
            {
                this.x = x;
                this.y = y;
            }
        }

        [StructLayout(LayoutKind.Sequential)]
        public struct MINMAXINFO
        {
            public POINT ptReserved;
            public POINT ptMaxSize;
            public POINT ptMaxPosition;
            public POINT ptMinTrackSize;
            public POINT ptMaxTrackSize;
        };

        void win_Loaded(object sender, RoutedEventArgs e)
        {
            this.WindowState = WindowState.Maximized;
        }


        /// <summary>
        /// </summary>
        [StructLayout(LayoutKind.Sequential, CharSet = CharSet.Auto)]
        public class MONITORINFO
        {
            /// <summary>
            /// </summary>            
            public int cbSize = Marshal.SizeOf(typeof(MONITORINFO));

            /// <summary>
            /// </summary>            
            public RECT rcMonitor = new RECT();

            /// <summary>
            /// </summary>            
            public RECT rcWork = new RECT();

            /// <summary>
            /// </summary>            
            public int dwFlags = 0;
        }


        /// <summary> Win32 </summary>
        [StructLayout(LayoutKind.Sequential, Pack = 0)]
        public struct RECT
        {
            /// <summary> Win32 </summary>
            public int left;
            /// <summary> Win32 </summary>
            public int top;
            /// <summary> Win32 </summary>
            public int right;
            /// <summary> Win32 </summary>
            public int bottom;

            /// <summary> Win32 </summary>
            public static readonly RECT Empty = new RECT();

            /// <summary> Win32 </summary>
            public int Width
            {
                get { return Math.Abs(right - left); }  // Abs needed for BIDI OS
            }
            /// <summary> Win32 </summary>
            public int Height
            {
                get { return bottom - top; }
            }

            /// <summary> Win32 </summary>
            public RECT(int left, int top, int right, int bottom)
            {
                this.left = left;
                this.top = top;
                this.right = right;
                this.bottom = bottom;
            }


            /// <summary> Win32 </summary>
            public RECT(RECT rcSrc)
            {
                this.left = rcSrc.left;
                this.top = rcSrc.top;
                this.right = rcSrc.right;
                this.bottom = rcSrc.bottom;
            }

            /// <summary> Win32 </summary>
            public bool IsEmpty
            {
                get
                {
                    // BUGBUG : On Bidi OS (hebrew arabic) left > right
                    return left >= right || top >= bottom;
                }
            }
            /// <summary> Return a user friendly representation of this struct </summary>
            public override string ToString()
            {
                if (this == RECT.Empty) { return "RECT {Empty}"; }
                return "RECT { left : " + left + " / top : " + top + " / right : " + right + " / bottom : " + bottom + " }";
            }

            /// <summary> Determine if 2 RECT are equal (deep compare) </summary>
            public override bool Equals(object obj)
            {
                if (!(obj is Rect)) { return false; }
                return (this == (RECT)obj);
            }

            /// <summary>Return the HashCode for this struct (not garanteed to be unique)</summary>
            public override int GetHashCode()
            {
                return left.GetHashCode() + top.GetHashCode() + right.GetHashCode() + bottom.GetHashCode();
            }


            /// <summary> Determine if 2 RECT are equal (deep compare)</summary>
            public static bool operator ==(RECT rect1, RECT rect2)
            {
                return (rect1.left == rect2.left && rect1.top == rect2.top && rect1.right == rect2.right && rect1.bottom == rect2.bottom);
            }

            /// <summary> Determine if 2 RECT are different(deep compare)</summary>
            public static bool operator !=(RECT rect1, RECT rect2)
            {
                return !(rect1 == rect2);
            }


        }

        [DllImport("user32")]
        internal static extern bool GetMonitorInfo(IntPtr hMonitor, MONITORINFO lpmi);

        /// <summary>
        /// 
        /// </summary>
        [DllImport("User32")]
        internal static extern IntPtr MonitorFromWindow(IntPtr handle, int flags);

        private void Window_MouseDoubleClick(object sender, MouseButtonEventArgs e)
        {

        }

        private void cboInstances_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {


            try
            {
                Service instance = (cboInstances.SelectedItem as Service);

                if (instance != null)
                {
                    var qry = from db in dc.Databases
                              where db.ServiceID == instance.ServiceID
                              select db;

                    List<Database> databases = qry.ToList<Database>();
                    Database dummy = new Database { DatabaseID = -1, DatabaseName = "All" };

                    databases.Insert(0, dummy);

                    cboDatabases.DisplayMemberPath = "DatabaseName";
                    cboDatabases.ItemsSource = databases;
                }
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
        }




        private void btnShowReport_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                if (cboReports.SelectedIndex >= 0)
                {
                    String strReport = (cboReports.SelectedItem as ComboBoxItem).Content.ToString();

                    if (strReport == "Servers and services")
                    {
                        this.OverviewReports.ShowServersAndServices();
                    }
                    else if (strReport == "Databases and files")
                    {
                        this.OverviewReports.ShowDatabases();
                    }
                    else if (strReport == "Index Fragmentation")
                    {
                        this.OverviewReports.ShowIndexes();
                    }
                    else if (strReport == "SQL Agent Jobs")
                    {
                        this.OverviewReports.ShowSQLAgentJobs();
                    }
                    else if (strReport == "Unused Non Clustered Indexes")
                    {
                        this.OverviewReports.ShowUnusedNonClusteredIndexes();
                    }


                }
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
        }

        private void checkdatabase_AccessiblityResult(bool accessible)
        {
            try
            {

                if (accessible)
                {
                    dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings.Get("ConnectionStringSSHCDatabase"));

                    var qrytraces = from dt in dc.DatabaseTraces
                                    select dt;


                    if (qrytraces.Count() > 0)
                    {

                        this.LstBox_tracing.ItemContainerGenerator.StatusChanged += new EventHandler(ItemContainerGenerator_StatusChanged);

                        foreach (DatabaseTrace databasetrace in qrytraces)
                        {
                            this.TraceSQLInstances.Insert(TraceSQLInstances.Count, databasetrace);
                        }

                    }




                }

            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
        }

        void ItemContainerGenerator_StatusChanged(object sender, EventArgs e)
        {
            if (sender == this.TraceStatusCheckTimer)
            {
                traceRefreshSender = true;

            }
            if (this.LstBox_tracing.ItemContainerGenerator.Status == System.Windows.Controls.Primitives.GeneratorStatus.ContainersGenerated)
            {


                Dispatcher.BeginInvoke(System.Windows.Threading.DispatcherPriority.Input, new System.Action(LoadControlsDelayedAction));

            }

        }


        void LoadControlsDelayedAction()
        {
            try
            {
                lock (this)
                {
                    bool success = true;

                    foreach (DatabaseTrace databasetrace in LstBox_tracing.Items)
                    {
                        ListBoxItem ListBoxItem = (ListBoxItem)(this.LstBox_tracing.ItemContainerGenerator.ContainerFromItem(databasetrace) as ListBoxItem);
                        if (ListBoxItem == null)
                        {
                            success = false;
                        }
                        else
                        {
                            ContentPresenter ContentPresenter = FindVisualChild<ContentPresenter>(ListBoxItem);
                            DataTemplate TracingDataTemplate = ContentPresenter.ContentTemplate;
                            Expander TracingExpander = (Expander)TracingDataTemplate.FindName("Tracing_expander", ContentPresenter);
                            System.Windows.Controls.ComboBox InstanceComboBox = (System.Windows.Controls.ComboBox)TracingExpander.FindName("combobox_tracing_instance");
                            if (InstanceComboBox == null) return;

                            System.Windows.Controls.ComboBox DatabaseComboBox = (System.Windows.Controls.ComboBox)TracingExpander.FindName("combobox_tracing_database");
                            if (DatabaseComboBox == null) return;

                            DatePicker startDate = (DatePicker)TracingDataTemplate.FindName("calendar_Startdate", ContentPresenter);
                            if (startDate == null) return;

                            DatePicker endDate = (DatePicker)TracingDataTemplate.FindName("calendar_Enddate", ContentPresenter);
                            if (startDate == null) return;

                            System.Windows.Controls.ComboBox starttime_hourComboBox = (System.Windows.Controls.ComboBox)TracingDataTemplate.FindName("Time_StartTime_Hour", ContentPresenter);
                            if (starttime_hourComboBox == null) return;

                            System.Windows.Controls.ComboBox endtime_minComboBox = (System.Windows.Controls.ComboBox)TracingDataTemplate.FindName("Time_StartTime_Min", ContentPresenter);
                            if (endtime_minComboBox == null) return;

                            System.Windows.Controls.ComboBox Time_EndTime_Hour = (System.Windows.Controls.ComboBox)TracingDataTemplate.FindName("Time_EndTime_Hour", ContentPresenter);
                            if (Time_EndTime_Hour == null) return;

                            System.Windows.Controls.ComboBox Time_EndTime_Min = (System.Windows.Controls.ComboBox)TracingDataTemplate.FindName("Time_EndTime_Min", ContentPresenter);
                            if (Time_EndTime_Min == null) return;

                            Button scheduleButton = (Button)TracingDataTemplate.FindName("sceduleButton", ContentPresenter);
                            if (scheduleButton == null) return;

                            if (!traceRefreshSender)
                            {

                                TracingExpander.IsExpanded = false;
                            }



                            if (databasetrace.State != DatabaseTraceState.New)
                            {

                                // Set calender selected date values.                           
                                startDate.SelectedDate = databasetrace.TraceStart.Value;
                                endDate.SelectedDate = databasetrace.TraceEnd.Value;

                                // Set Start and End hours and minutes
                                starttime_hourComboBox.SelectedIndex = databasetrace.TraceStart.Value.Hour;
                                endtime_minComboBox.SelectedIndex = databasetrace.TraceStart.Value.Minute;
                                Time_EndTime_Hour.SelectedIndex = databasetrace.TraceEnd.Value.Hour;
                                Time_EndTime_Min.SelectedIndex = databasetrace.TraceEnd.Value.Minute;

                                // load service
                                List<Service> tracingservices = new List<Service>();
                                tracingservices.Insert(0, databasetrace.Database.Service);
                                InstanceComboBox.ItemsSource = tracingservices;
                                InstanceComboBox.DisplayMemberPath = "InstanceName";
                                InstanceComboBox.SelectedValuePath = "InstanceName";
                                InstanceComboBox.SelectedIndex = 0;

                                // load database 
                                List<Database> databases = new List<Database>();
                                databases.Insert(0, databasetrace.Database);
                                DatabaseComboBox.ItemsSource = databases;
                                DatabaseComboBox.DisplayMemberPath = "DatabaseName";
                                DatabaseComboBox.SelectedValuePath = "DatabaseName";
                                DatabaseComboBox.SelectedIndex = 0;

                                // Disable editing for all tracing controls                           
                                //InstanceComboBox.IsEditable = false;
                                //DatabaseComboBox.IsEditable = false;
                                //startDate.IsEnabled = false;
                                //endDate.IsEnabled= false;
                                //starttime_hourComboBox.IsEditable = false;
                                //endtime_minComboBox.IsEditable = false;
                                //Time_EndTime_Hour.IsEditable = false;
                                //Time_EndTime_Min.IsEditable = false;

                                // change Image of schedule button to Show report image 
                                if (databasetrace.State == DatabaseTraceState.Finished)
                                {
                                    string imageUri = "pack://application:,,,/SQLServerOptimizer.PL.WPF;component/Images/reports.png";
                                    scheduleButton.IsEnabled = true;
                                    scheduleButton.ToolTip = "Show tracing Reports";
                                    Image _imgrestor_down = (Image)TracingDataTemplate.FindName("image_capturetrace", ContentPresenter);
                                    if (_imgrestor_down == null) return;
                                    _imgrestor_down.Source = new ImageSourceConverter().ConvertFromString(imageUri) as ImageSource;



                                    Image _traceStatusIMG = (Image)TracingDataTemplate.FindName("TraceStatusIMG", ContentPresenter);
                                    _traceStatusIMG.Visibility = Visibility.Visible;
                                    if (_traceStatusIMG == null) return;
                                    _traceStatusIMG.Source = new ImageSourceConverter().ConvertFromString(imageUri) as ImageSource;

                                    Label labelTracingUpdate = (Label)scheduleButton.FindName("traceProgressupdate");
                                    if (labelTracingUpdate == null)
                                        return;

                                    labelTracingUpdate.Content = "Trace is Finished.";
                                }
                                else if (databasetrace.State == DatabaseTraceState.IsRunning)
                                {

                                    string imageUri = "pack://application:,,,/SQLServerOptimizer.PL.WPF;component/Images/tracerunning.png";
                                    scheduleButton.IsEnabled = false;
                                    scheduleButton.ToolTip = "Trace is Running";

                                    Image _imgrestor_down = (Image)scheduleButton.FindName("image_capturetrace");
                                    if (_imgrestor_down == null) return;
                                    _imgrestor_down.Source = new ImageSourceConverter().ConvertFromString(imageUri) as ImageSource;

                                    Image _traceStatusIMG = (Image)TracingDataTemplate.FindName("TraceStatusIMG", ContentPresenter);
                                    _traceStatusIMG.Visibility = Visibility.Visible;
                                    if (_traceStatusIMG == null) return;
                                    _traceStatusIMG.Source = new ImageSourceConverter().ConvertFromString(imageUri) as ImageSource;

                                    Label labelTracingUpdate = (Label)scheduleButton.FindName("traceProgressupdate");
                                    if (labelTracingUpdate == null)
                                        return;


                                    labelTracingUpdate.Content = "Trace is Running.";
                                }
                                else if (databasetrace.State == DatabaseTraceState.Scheduled)
                                {

                                    string imageUri = "pack://application:,,,/SQLServerOptimizer.PL.WPF;component/Images/tracerunning.png";
                                    scheduleButton.IsEnabled = false;
                                    scheduleButton.ToolTip = "Trace is Scheduled";

                                    Image _imgrestor_down = (Image)scheduleButton.FindName("image_capturetrace");
                                    if (_imgrestor_down == null) return;
                                    _imgrestor_down.Source = new ImageSourceConverter().ConvertFromString(imageUri) as ImageSource;

                                    Image _traceStatusIMG = (Image)TracingDataTemplate.FindName("TraceStatusIMG", ContentPresenter);
                                    _traceStatusIMG.Visibility = Visibility.Visible;
                                    if (_traceStatusIMG == null) return;
                                    _traceStatusIMG.Source = new ImageSourceConverter().ConvertFromString(imageUri) as ImageSource;
                                    Label labelTracingUpdate = (Label)scheduleButton.FindName("traceProgressupdate");
                                    if (labelTracingUpdate == null)
                                        return;


                                    labelTracingUpdate.Content = "Trace is Scheduled.";
                                }
                            }
                            else
                            {


                                TracingExpander.IsExpanded = true;
                            }
                        }


                    }
                    if (success)
                        this.LstBox_tracing.ItemContainerGenerator.StatusChanged -= ItemContainerGenerator_StatusChanged;
                }
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
            finally
            {

                traceRefreshSender = !traceRefreshSender;
            }
        }
        private void btnSaveResults_Click(object sender, System.Windows.RoutedEventArgs e)
        {
            try
            {
                SaveFileDialog saveDatabasebackup = new SaveFileDialog();
                saveDatabasebackup.Filter = "Database Backup|*.bak";
                saveDatabasebackup.Title = "Save a Database Snap Shot";
                saveDatabasebackup.ShowDialog();

                if (saveDatabasebackup.FileName != "")
                {


                    try
                    {
                        BackupRestore.BackupDatabase(saveDatabasebackup.FileName);

                    }

                    catch (Exception ex)
                    {



                    }
                }

            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }

        }

        private void btnRestore_Click(object sender, System.Windows.RoutedEventArgs e)
        {
            try
            {

                OpenFileDialog openFileDialog1 = new OpenFileDialog();

                openFileDialog1.InitialDirectory = "c:\\";
                openFileDialog1.Filter = "bak files (*.bak)|*.bak|All files (*.*)|*.*";
                openFileDialog1.FilterIndex = 2;
                openFileDialog1.RestoreDirectory = true;

                if (openFileDialog1.ShowDialog().HasValue)
                {
                    if (!String.IsNullOrEmpty(openFileDialog1.FileName))
                    {
                        try
                        {

                            BackupRestore.RestoreDatabase(openFileDialog1.FileName);

                            this.OverviewReports.UserControl_Loaded(null, null);
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message);
                            Logger.Error(ex);
                        }


                        finally
                        {
                            IssueReport.RefreshReport();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
        }
        DatabaseTrace traceInstance;
        private void btn_tracing_Add_Click(object sender, RoutedEventArgs e)
        {
            //Creates new TraceServerInstace Object
            try
            {


                    traceInstance = new DatabaseTrace();
                    DateTime Startdate = DateTime.Now;
                    DateTime Enddate = DateTime.Now;
                    //traceInstance.TraceStart = Startdate.Date;
                    // traceInstance.TraceEnd = Enddate.Date;

                    this.TraceSQLInstances.Insert(TraceSQLInstances.Count, traceInstance);

                    System.Threading.ThreadPool.QueueUserWorkItem(
                            (a) =>
                            {
                                try
                                {
                                    System.Threading.Thread.Sleep(100);
                                    CheckInstanceStatusWorker checkOptimizerDBStatus = new CheckInstanceStatusWorker(this, System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"].ToString());

                                    checkOptimizerDBStatus.AccessiblityResult += new InstanceStatusResult(checkOptimizerDBStatus_AccessiblityResult);
                                    checkOptimizerDBStatus.Start();

                                }
                                catch (Exception ex)
                                {
                                    Logger.Error(ex);
                                }

                            }
                            );
                
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }

        }

        void checkOptimizerDBStatus_AccessiblityResult(bool accessible)
        {
            try
            {
                if (accessible)
                {

                    ListBoxItem ListBoxtraceItem = (ListBoxItem)(this.LstBox_tracing.ItemContainerGenerator.ContainerFromItem(traceInstance) as ListBoxItem);
                    if (ListBoxtraceItem == null)
                        return;
                    ContentPresenter ContentPresenter = FindVisualChild<ContentPresenter>(ListBoxtraceItem);

                    DataTemplate TracingDataTemplate = ContentPresenter.ContentTemplate;
                    Expander expander = (Expander)TracingDataTemplate.FindName("Tracing_expander", ContentPresenter);
                    ComboBox InstanceComboBox = (ComboBox)TracingDataTemplate.FindName("combobox_tracing_instance", ContentPresenter);
                    ComboBox DatabaseComboBox = (ComboBox)TracingDataTemplate.FindName("combobox_tracing_database", ContentPresenter);

                    DatePicker startDate = (DatePicker)TracingDataTemplate.FindName("calendar_Startdate", ContentPresenter);
                    if (startDate == null) return;

                    DatePicker endDate = (DatePicker)TracingDataTemplate.FindName("calendar_Enddate", ContentPresenter);
                    if (startDate == null) return;

                    System.Windows.Controls.ComboBox starttime_hourComboBox = (System.Windows.Controls.ComboBox)TracingDataTemplate.FindName("Time_StartTime_Hour", ContentPresenter);
                    if (starttime_hourComboBox == null) return;

                    System.Windows.Controls.ComboBox endtime_minComboBox = (System.Windows.Controls.ComboBox)TracingDataTemplate.FindName("Time_StartTime_Min", ContentPresenter);
                    if (endtime_minComboBox == null) return;

                    System.Windows.Controls.ComboBox Time_EndTime_Hour = (System.Windows.Controls.ComboBox)TracingDataTemplate.FindName("Time_EndTime_Hour", ContentPresenter);
                    if (Time_EndTime_Hour == null) return;

                    System.Windows.Controls.ComboBox Time_EndTime_Min = (System.Windows.Controls.ComboBox)TracingDataTemplate.FindName("Time_EndTime_Min", ContentPresenter);
                    if (Time_EndTime_Min == null) return;

                    DateTime startTime = DateTime.Now.AddMinutes(5);
                    DateTime endTime = startTime.AddMinutes(5);

                    startDate.SelectedDate = startTime.Date;

                    endDate.SelectedDate = endTime.Date;

                    starttime_hourComboBox.SelectedIndex = startTime.Hour;

                    endtime_minComboBox.SelectedIndex = startTime.Minute;

                    Time_EndTime_Hour.SelectedIndex = endTime.Hour;

                    Time_EndTime_Min.SelectedIndex = endTime.Minute;

                    Window mainWindow = Window.GetWindow(expander);

                    //Get
                    HealthCheckDataContext dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"].ToString());

                    var qry = from s in dc.Services
                              where s.ServiceTypeID == 1
                              select s;



                    InstanceComboBox.DisplayMemberPath = "InstanceName";
                    InstanceComboBox.ItemsSource = qry.ToList();

                }
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }




        }

        // Removes the selected Item from the Tracing List Box
        private void btn_tracing_Remove_Click(object sender, RoutedEventArgs e)
        {

            int databaseTraceId = 0;
            try
            {
                while (this.LstBox_tracing.SelectedItems.Count > 0)
                {

                    DatabaseTrace selectedTrace = (DatabaseTrace)LstBox_tracing.SelectedItem;
                    databaseTraceId = selectedTrace.DatabaseTraceId;

                    if (databaseTraceId > 0)
                    {

                        DatabaseTrace databasetrace = dc.DatabaseTraces.FirstOrDefault(dt => dt.DatabaseTraceId == databaseTraceId);

                        SQLTrace.DeleteScheduledTraceJobfromServer(databasetrace);


                        foreach (DatabaseTraceDetail databasetracedetail in databasetrace.DatabaseTraceDetails)
                        {
                            dc.DatabaseTraceDetails.DeleteOnSubmit(databasetracedetail);

                        }
                        dc.DatabaseTraces.DeleteOnSubmit(databasetrace);
                        this.TraceSQLInstances.Remove((DatabaseTrace)LstBox_tracing.SelectedItem);

                        dc.SubmitChanges();
                    }
                    else
                    {
                        this.TraceSQLInstances.Remove((DatabaseTrace)LstBox_tracing.SelectedItem);


                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
        }
        // Removes ALL Items from the Tracing List Box
        private void btn_tracing_RemoveAll_Click(object sender, RoutedEventArgs e)
        {
            try
            {

                MessageBoxResult result = MessageBox.Show("All scheduled tracing and details would be removed from the database, would you like to continue ?", "Delete ALL Scheduled Database trace details.", MessageBoxButton.YesNo);

                if (result == MessageBoxResult.Yes)
                {

                    foreach (DatabaseTrace databasetrace in TraceSQLInstances)
                    {

                        SQLTrace.DeleteScheduledTraceJobfromServer(databasetrace);

                    }

                    dc.DatabaseTraceDetails.DeleteAllOnSubmit(dc.DatabaseTraceDetails);
                    dc.DatabaseTraces.DeleteAllOnSubmit(dc.DatabaseTraces);
                    dc.SubmitChanges();
                    this.TraceSQLInstances.Clear();

                }

                else if (result == MessageBoxResult.No)
                {

                    // No code here

                }

            }
            catch (Exception ex)
            {
                Logger.Error(ex);


            }
        }
        private void Tracing_Loaded(object sender, RoutedEventArgs e)
        {

            try
            {
                this.LstBox_tracing.DataContext = this.TraceSQLInstances;
                TraceStatusCheckTimer.Start();

                if (IsDatabaseCreated() && !fisttimeisloaded)
                {
                    string conn = System.Configuration.ConfigurationManager.AppSettings.Get("ConnectionStringSSHCDatabase");
                    CheckInstanceStatusWorker checkinstanceStatusworker = new CheckInstanceStatusWorker(this, conn);

                    checkinstanceStatusworker.AccessiblityResult += new InstanceStatusResult(checkdatabase_AccessiblityResult);
                    checkinstanceStatusworker.Start();
                    fisttimeisloaded = true;
                }


            }
            catch (Exception sql)
            {
                Logger.Error(sql);


            }


        }

        private void Image_ImageFailed(object sender, ExceptionRoutedEventArgs e)
        {

        }

        private void image_start_ImageFailed(object sender, ExceptionRoutedEventArgs e)
        {

        }

        private void Image_MouseLeftButtonDown(object sender, MouseButtonEventArgs e)
        {
            System.Diagnostics.Process.Start("http://www.sqlserveroptimizer.com/");
        }

        private void Ribbon_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            if (isloaded)
            {
                if (reportRibbonTab.IsSelected)
                {
                    report.Visibility = Visibility.Visible;
                    SettingsGrid.Visibility = Visibility.Collapsed;

                    TracingtabGrid.Visibility = Visibility.Collapsed;
                    Optimize.Visibility = Visibility.Collapsed;
                }
                else if (TracingRibbontab.IsSelected)
                {
                    SettingsGrid.Visibility = Visibility.Collapsed;
                    report.Visibility = Visibility.Collapsed;
                    TracingtabGrid.Visibility = Visibility.Visible;
                    Optimize.Visibility = Visibility.Collapsed;
                }
                else if (OptimizeRibbonTab.IsSelected)
                {
                    SettingsGrid.Visibility = Visibility.Collapsed;
                    report.Visibility = Visibility.Collapsed;
                    TracingtabGrid.Visibility = Visibility.Collapsed;
                    Optimize.Visibility = Visibility.Visible;

                }
                else if (Settings.IsSelected)
                {
                    SettingsGrid.Visibility = Visibility.Visible;
                    report.Visibility = Visibility.Collapsed;
                    TracingtabGrid.Visibility = Visibility.Collapsed;
                    Optimize.Visibility = Visibility.Collapsed;

                }
            }

        }

        private void reportRibbonTab_MouseLeftButtonDown(object sender, MouseButtonEventArgs e)
        {



        }

        private void RibbonTab_MouseLeftButtonDown(object sender, MouseButtonEventArgs e)
        {

        }

        private void Window_StateChanged(object sender, EventArgs e)
        {
            if (this.WindowState == WindowState.Maximized)
            {

                string imageUri2 = "pack://application:,,,/SQLServerOptimizer.PL.WPF;component/Images/change.png";



                var button = this.btnChangeView as Button;

                button.ToolTip = "Restore Down";

                Image _imgrestor_down = (Image)button.FindName("restore_down");

                _imgrestor_down.Source = new ImageSourceConverter().ConvertFromString(imageUri2) as ImageSource;
            }
        }



        private void tabControl1_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            if (isloaded)
            {
                if (tabScan.IsSelected)
                {
                    AutofixGroup.Visibility = Visibility.Collapsed;
                    Scangroup.Visibility = Visibility.Visible;
                    Schedulegroup.Visibility = Visibility.Collapsed;
                }
                else if (tabIssues.IsSelected)
                {

                    Scangroup.Visibility = Visibility.Collapsed;
                    AutofixGroup.Visibility = Visibility.Visible;
                    FixButtonGroup.Visibility = Visibility.Collapsed;
                    Schedulegroup.Visibility = Visibility.Collapsed;
                }
                else if (tabFixes.IsSelected)
                {

                    Scangroup.Visibility = Visibility.Collapsed;
                    AutofixGroup.Visibility = Visibility.Visible;
                    FixButtonGroup.Visibility = Visibility.Visible;
                    Schedulegroup.Visibility = Visibility.Collapsed;
                }
                else if (tabScheduling.IsSelected)
                {

                    Scangroup.Visibility = Visibility.Collapsed;
                    AutofixGroup.Visibility = Visibility.Collapsed;
                    FixButtonGroup.Visibility = Visibility.Collapsed;
                    Schedulegroup.Visibility = Visibility.Visible;
                }
            }
            else
            {
                Schedulegroup.Visibility = Visibility.Collapsed;
                AutofixGroup.Visibility = Visibility.Collapsed;
                Scangroup.Visibility = Visibility.Visible;
            }
        }


        private void combobox_servicefilter_SelectionChangedA(object sender, SelectionChangedEventArgs e)
        {
            try
            {


                if (this.combobox_servicefilterA.SelectedIndex != -1)
                {
                    try
                    {
                        this.AutoFix.instance = (combobox_servicefilterA.SelectedItem as Service);
                        var service = from ser in dc.Services
                                      where ser.ServiceID == this.AutoFix.instance.ServiceID
                                      select ser;
                        foreach (var ser in service)
                        {
                            AutoFixControl.serviceId = ser.ServiceID;
                        }
                        if (service.Count() == 0)
                        {
                            AutoFixControl.serviceId = -1;
                        }
                        if (AutoFixControl.serviceId != -1)
                        {
                            var qry = from db in dc.Databases
                                      where db.ServiceID == AutoFixControl.serviceId
                                      select db;

                            List<Database> databases = qry.ToList<Database>();
                            Database dummy = new Database { DatabaseID = -1, DatabaseName = "All" };

                            databases.Insert(0, dummy);

                            this.combobox_databasefilterA.ItemsSource = databases;
                            this.combobox_databasefilterA.DisplayMemberPath = "DatabaseName";

                        }
                    }
                    catch (Exception ex)
                    {
                        Logger.Error(ex);
                    }
                }
                this.AutoFix.combobox_servicefilter.SelectedIndex = combobox_servicefilterA.SelectedIndex;
                this.AutoFix.RefreshReport();
                // this.combobox_databasefilter.SelectedIndex = 0;
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
        }

        private void combobox_databasefilter_SelectionChangedA(object sender, SelectionChangedEventArgs e)
        {

            try
            {


                if (this.combobox_databasefilterA.SelectedIndex != -1)
                {
                    try
                    {


                        if (this.combobox_databasefilterA.SelectedIndex != -1)
                        {
                            this.AutoFix.database = (this.combobox_databasefilterA.SelectedItem as Database);
                            AutoFixControl.databaseId = this.AutoFix.database.DatabaseID;


                        }
                    }
                    catch (Exception ex)
                    {
                        Logger.Error(ex);
                    }
                }
                this.AutoFix.combobox_databasefilter.SelectedIndex = combobox_databasefilterA.SelectedIndex;
                this.AutoFix.RefreshReport();
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
        }
        private void combobox_Expand_SelectionChangedA(object sender, SelectionChangedEventArgs e)
        {
            if (isloaded)
            {
                AutoFixControl.expandlevel = combobox_Expand.SelectedIndex;
                this.AutoFix.combobox_Expand.SelectedIndex = combobox_ExpandA.SelectedIndex;
                this.AutoFix.RefreshReport();
            }
        }

        private void button1A_Click(object sender, RoutedEventArgs e)
        {
            IssueViewModel a = (IssueViewModel)this.AutoFix.tree.Items[0];
            fixer fxr = new fixer();
            fxr.fixissues(a);
            this.AutoFix.RefreshReport();


        }
        public void FillSQLServicesComboboxA()
        {
            try
            {
                string conn = System.Configuration.ConfigurationManager.AppSettings.Get("ConnectionStringSSHCDatabase");
                CheckInstanceStatusWorker checkinstanceStatusworker = new CheckInstanceStatusWorker(this, conn);

                checkinstanceStatusworker.AccessiblityResult += new InstanceStatusResult(checkinstanceStatusworker_AccessiblityResultA);
                checkinstanceStatusworker.Start();
            }
            catch (Exception sql)
            {
                Logger.Error(sql);
            }
        }
        private void checkinstanceStatusworker_AccessiblityResultA(bool accessible)
        {
            try
            {

                if (accessible)
                {
                    dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings.Get("ConnectionStringSSHCDatabase"));

                    var qry = from i in dc.Services
                              where i.ServiceTypeID == 1
                              select i;
                    this.AutoFix.SQLServerInstances = qry.ToList<Service>();
                    Server dummy = new Server { Name = "All" };
                    Service dummyService = new Service { ServiceID = -1, Server = dummy, Name = "" };

                    this.AutoFix.SQLServerInstances.Insert(0, dummyService);

                    this.combobox_servicefilterA.ItemsSource = this.AutoFix.SQLServerInstances;
                    this.combobox_servicefilterA.DisplayMemberPath = "InstanceName";
                    this.combobox_servicefilterA.SelectedValuePath = "ServiceID";

                    this.combobox_servicefilterA.SelectedIndex = 0;

                    List<Database> databases = new List<Database>();
                    Database dummyDatabase = new Database { DatabaseID = -1, DatabaseName = "All" };

                    databases.Insert(0, dummyDatabase);


                    this.combobox_databasefilterA.ItemsSource = databases;
                    this.combobox_databasefilterA.DisplayMemberPath = "DatabaseName";

                    this.combobox_databasefilterA.SelectedIndex = 0;

                }
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
        }

        private void btnLoadA_Click(object sender, RoutedEventArgs e)
        {
            this.AutoFix.RefreshReport();
        }

        private void tabControl2_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {


            if (isloaded)
            {
                if (tabTracingitem.IsSelected)
                {
                    Tracinggroud.Visibility = Visibility.Visible;
                    PerformanceSchd.Visibility = Visibility.Collapsed;
                    DashboardGroup.Visibility = Visibility.Collapsed;
                }

                else if (Performance.IsSelected)
                {
                    PerformanceSchd.Visibility = Visibility.Visible;
                    Tracinggroud.Visibility = Visibility.Collapsed;
                    DashboardGroup.Visibility = Visibility.Collapsed;
                }
                else if (PerformanceResult.IsSelected)
                {
                    PerformanceSchd.Visibility = Visibility.Collapsed;
                    Tracinggroud.Visibility = Visibility.Collapsed;
                    DashboardGroup.Visibility = Visibility.Visible;
                }
                else if (PerformanceResult_Waitstats.IsSelected)
                {
                    PerformanceSchd.Visibility = Visibility.Collapsed;
                    Tracinggroud.Visibility = Visibility.Collapsed;
                    DashboardGroup.Visibility = Visibility.Visible;
                }
                else
                {
                    PerformanceSchd.Visibility = Visibility.Collapsed;
                    Tracinggroud.Visibility = Visibility.Collapsed;
                    DashboardGroup.Visibility = Visibility.Collapsed;
                }

            }
        }

        private void combobox_databasefilter_SelectionChangedb(object sender, SelectionChangedEventArgs e)
        {
            combobox_databasefilter_SelectionChangedA(sender, e);
            combobox_databasefilter_SelectionChanged(sender, e);
        }

        private void combobox_Expand_SelectionChangedb(object sender, SelectionChangedEventArgs e)
        {
            combobox_Expand_SelectionChangedA(sender, e);
            combobox_Expand_SelectionChanged(sender, e);
        }

        private void combobox_servicefilter_SelectionChangedb(object sender, SelectionChangedEventArgs e)
        {
            combobox_servicefilter_SelectionChangedA(sender, e);
            combobox_servicefilter_SelectionChanged(sender, e);
        }

        private void Ribbon_MouseRightButtonDown(object sender, System.Windows.Input.MouseButtonEventArgs e)
        {
            e.Handled = true;
        }



        private void From_TextChanged(object sender, TextChangedEventArgs e)
        {

            var text = sender as TextBox;
            text.Foreground = Brushes.Black;
        }

        private void slctall_Checked(object sender, RoutedEventArgs e)
        {
            if (IsLoaded)
            {
                this.IssueSetting.go((bool)slctall.IsChecked);
            }
        }

        private void chkcatagorie_Checked(object sender, RoutedEventArgs e)
        {

        }

        private void tabSettings_SelectionChanged(object sender, System.Windows.Controls.SelectionChangedEventArgs e)
        {
            if (tabIssuetypes.IsSelected)
            {
                Issuetypesettingsgroup.Visibility = Visibility.Visible;
                EmailSettingsGroup.Visibility = Visibility.Collapsed;
            }
            else
            {
                var email = from em in dc.Emails
                            select em;

                foreach (var item in email)
                {
                    this.From.Text = item.From;
                    this.To.Text = item.To;
                    this.Username.Text = item.Username;
                    this.Password.Password = item.Password;
                    this.Host.Text = item.Host;
                    this.Port.Text = item.Port;
                }

                Issuetypesettingsgroup.Visibility = Visibility.Collapsed;
                EmailSettingsGroup.Visibility = Visibility.Visible;

            }


        }



        private void btnSaveInstancesSched_Click(object sender, System.Windows.RoutedEventArgs e)
        {
            // TODO: Add event handler implementation here.
        }

        private void btnImportSched_Click(object sender, System.Windows.RoutedEventArgs e)
        {
            // TODO: Add event handler implementation here.
        }

        private void btnaddnewSched_Click(object sender, System.Windows.RoutedEventArgs e)
        {
            SQLServerInstance SqlServerInstance = new SQLServerInstance();
            SqlServerInstance.InstanceName = "<New Instance>";
            SqlServerInstance.Port = "1433";
            SqlServerInstance.Authentication = "Windows Authentication";
            SqlServerInstance.Login = "";

            SqlServerInstance.Password = "";

            this.SQLServerScheduleInstances.Insert(SQLServerScheduleInstances.Count, SqlServerInstance);
        }

        private void btnClearSched_Click(object sender, System.Windows.RoutedEventArgs e)
        {
            if (Listbox_Schedule.SelectedItems.Count > 0)
            {

                this.SQLServerScheduleInstances.Remove((SQLServerInstance)Listbox_Schedule.SelectedItem);


            }
        }

        private void btnRemoveallSched_Click(object sender, System.Windows.RoutedEventArgs e)
        {
            this.SQLServerScheduleInstances.Clear();
        }

        private void btnMailSettings_Click(object sender, System.Windows.RoutedEventArgs e)
        {
            Settings.IsSelected = true;
            tabEmail.IsSelected = true;
        }

        private void Select_issues(object sender, SelectionChangedEventArgs e)
        {
            if (IsLoaded)
            {
                List<bool> severity = new List<bool>();
                severity.Add(true);
                severity.Add((bool)this.Low.IsChecked);
                severity.Add((bool)this.Med.IsChecked);
                severity.Add((bool)this.High.IsChecked);

                List<bool> scope = new List<bool>();
                List<bool> catogory = new List<bool>();
                for (int i = 0; i < 12; i++)
                {
                    scope.Add(false);
                    catogory.Add(false);
                }
                scope.Insert(scopebox.SelectedIndex, true);
                catogory.Insert(catogoriebox.SelectedIndex, true);
                this.IssueSetting.go(scope, catogory, severity);
            }
        }

        private void CheckBox_Checked(object sender, System.Windows.RoutedEventArgs e)
        {
            if (IsLoaded)
            {
                this.dockpanelscheduling.IsEnabled = true;
            }
        }

        private void CheckBox_Unchecked(object sender, System.Windows.RoutedEventArgs e)
        {
            this.dockpanelscheduling.IsEnabled = false;
        }

        private void CheckBox_Severity_issuesettings(object sender, RoutedEventArgs e)
        {
            if (IsLoaded)
            {
                List<bool> severity = new List<bool>();
                severity.Add(true);
                severity.Add((bool)this.Low.IsChecked);
                severity.Add((bool)this.Med.IsChecked);
                severity.Add((bool)this.High.IsChecked);

                List<bool> scope = new List<bool>();
                List<bool> catogory = new List<bool>();
                for (int i = 0; i < 12; i++)
                {
                    scope.Add(false);
                    catogory.Add(false);
                }
                scope.Insert(scopebox.SelectedIndex, true);
                catogory.Insert(catogoriebox.SelectedIndex, true);
                this.IssueSetting.go(scope, catogory, severity);
            }
        }

        private void btnAddToscheduling_Click(object sender, System.Windows.RoutedEventArgs e)
        {
            this.tabScheduling.IsSelected = true;
            foreach (var item in SQLServerInstances)
            {

                if (!containsInstances(SQLServerScheduleInstances, item))
                {
                    SQLServerScheduleInstances.Add(item);

                }
            }

        }

        private bool containsInstances(ObservableCollection<SQLServerInstance> instances, SQLServerInstance b)
        {
            foreach (var a in instances)
            {


                if (a.Authentication == b.Authentication && ((a.Password == b.Password && a.Login == b.Login) || b.Authentication == "Windows Authentication") &&
                    a.InstanceName == b.InstanceName
                    && a.Port == b.Port)
                {
                    return true;
                }

            }
            return false;
        }

        private void Schedule_Click(object sender, RoutedEventArgs e)
        {
            if (Convert.ToBoolean(schedulingEnabled.IsChecked))
            {

                    var exsisting = from a in dc.ScheduledServices
                                    select a;
                    foreach (var item in exsisting)
                    {
                        dc.ScheduledServices.DeleteOnSubmit(item);
                        dc.SubmitChanges();

                    }

                    foreach (var scheduledInstance in SQLServerScheduleInstances)
                    {
                        ScheduledService scheduledService = new ScheduledService();
                        scheduledService.Name = scheduledInstance.InstanceName;
                        scheduledService.Authentication = scheduledInstance.Authentication;
                        scheduledService.Port = Convert.ToInt32(scheduledInstance.Port);
                        scheduledService.Password = scheduledInstance.Password;
                        scheduledService.Login = scheduledInstance.Login;
                        dc.ScheduledServices.InsertOnSubmit(scheduledService);
                        dc.SubmitChanges();
                    }
                    this.SchedulingControl.createtask();
                    if (SQLServerScheduleInstances.Count() > 0)
                        MessageBox.Show("Task Scheduled");
                    else
                        MessageBox.Show("You need to add Instances first");
                
            }
        }

        private void Listbox_Schedule_Loaded(object sender, RoutedEventArgs e)
        {
            if (Listbox_Schedule.Items.Count == 0)
            {
                var exsisting = from a in dc.ScheduledServices
                                select a;
                foreach (var item in exsisting)
                {
                    SQLServerScheduleInstances.Add(new SQLServerInstance { Authentication = item.Authentication, InstanceName = item.Name, Login = item.Login, Password = item.Password, Port = item.Port.ToString() });

                }

            }
        }

        private void btnSaveEmail_Click(object sender, System.Windows.RoutedEventArgs e)
        {
            dc.Emails.DeleteAllOnSubmit(dc.Emails);
            dc.SubmitChanges();
            Email mail = new Email();
            mail.From = ((System.Windows.Controls.TextBox)(gensettings.FindName("From"))).Text;
            mail.To = (((System.Windows.Controls.TextBox)(gensettings.FindName("To"))).Text);
            mail.Username = ((System.Windows.Controls.TextBox)(gensettings.FindName("Username"))).Text;
            mail.Password = ((System.Windows.Controls.PasswordBox)(gensettings.FindName("Password"))).Password;
            mail.Port = ((System.Windows.Controls.TextBox)(gensettings.FindName("Port"))).Text;
            mail.Host = ((System.Windows.Controls.TextBox)(gensettings.FindName("Host"))).Text;
            dc.Emails.InsertOnSubmit(mail);
            dc.SubmitChanges();

            MessageBox.Show("Email settings saved");

        }



        private void TestEmail_Click(object sender, System.Windows.RoutedEventArgs e)
        {

            string imageUri = "pack://application:,,,/SQLServerOptimizer.PL.WPF;component/Images/mail.gif";
            var button = this.TestEmail as Button;
            button.ToolTip = "Test Email";

            btnStartCheck.IsEnabled = true;
            Image _imgrestor_down = (Image)button.Template.FindName("MailSettings", button);
            if (_imgrestor_down != null)
            {
                _imgrestor_down.Source = new ImageSourceConverter().ConvertFromString(imageUri) as ImageSource;

            }
            try
            {

                Email email = new Email();
                email.From = ((System.Windows.Controls.TextBox)(gensettings.FindName("From"))).Text;
                email.To = (((System.Windows.Controls.TextBox)(gensettings.FindName("To"))).Text);
                email.Username = ((System.Windows.Controls.TextBox)(gensettings.FindName("Username"))).Text;
                email.Password = ((System.Windows.Controls.PasswordBox)(gensettings.FindName("Password"))).Password;
                email.Port = ((System.Windows.Controls.TextBox)(gensettings.FindName("Port"))).Text;
                email.Host = ((System.Windows.Controls.TextBox)(gensettings.FindName("Host"))).Text;

                new MailReport().mail(email.From, email.To, email.Username, email.Password, email.Host, email.Port, "issuereport" + DateTime.Now.Date.ToShortDateString());



            }
            catch (Exception mailexept)
            {
                MessageBox.Show(mailexept.Message);
            }
        }



        private void Button_Click(object sender, System.Windows.RoutedEventArgs e)
        {
            //new SchedulePerfTask().CreateTask();
            SQLServerInstance SqlServerInstance = new SQLServerInstance();
            SqlServerInstance.InstanceName = "<New Instance>";
            SqlServerInstance.Port = "1433";
            SqlServerInstance.Authentication = "Windows Authentication";
            SqlServerInstance.Login = "";

            SqlServerInstance.Password = "";


            this.PerformanceTraceInstances.Insert(PerformanceTraceInstances.Count, SqlServerInstance);
            this.PerformanceTracingListbox.DataContext = this.PerformanceTraceInstances;


        }


        private void PerfSchedule_Click(object sender, RoutedEventArgs e)
        {

            if ((from a in dc.Traces where a.Name == TraceNametxtbx.Text select a).Count() == 0 && TraceNametxtbx.Text.Replace(" ", "") != "")
            {
                if (PerformanceTraceInstances.Count() > 0)
                {
                    if (Convert.ToDateTime(this.StartDate.SelectedDate.Value.AddHours(Convert.ToDouble(StartHourCB.SelectedItem)).AddMinutes(Convert.ToDouble(StartMinCB.SelectedItem))) > Convert.ToDateTime(this.EndDate.SelectedDate.Value.AddHours(Convert.ToDouble(StartHourCB.SelectedItem)).AddMinutes(Convert.ToDouble(StartMinCB.SelectedItem))))
                    {
                        MessageBox.Show("End Date cannot be less than Start Date");
                    }
                    else if (Convert.ToDateTime(this.StartDate.SelectedDate.Value.AddHours(Convert.ToDouble(StartHourCB.SelectedItem)).AddMinutes(Convert.ToDouble(StartMinCB.SelectedItem))) > DateTime.Now)
                    {


                        Trace trace = new Trace();
                        trace.StartTime = Convert.ToDateTime(this.StartDate.SelectedDate.Value.AddHours(Convert.ToDouble(StartHourCB.SelectedItem)).AddMinutes(Convert.ToDouble(StartMinCB.SelectedItem)));
                        trace.StopTime = Convert.ToDateTime(this.EndDate.SelectedDate.Value.AddHours(Convert.ToDouble(EndHourCB.SelectedItem)).AddMinutes(Convert.ToDouble(EndMinCB.SelectedItem)));
                        trace.Name = TraceNametxtbx.Text;
                        dc.Traces.InsertOnSubmit(trace);
                        dc.SubmitChanges();
                        foreach (var traceInstances in PerformanceTraceInstances)
                        {
                            Instance instance = new Instance();
                            Instance_Trace InstanceTrace = new Instance_Trace();

                            var instanceExists = from a in dc.Instances
                                                 where a.Name.ToLower() == traceInstances.InstanceName.ToLower()
                                                 select a;
                            if (instanceExists.Count() != 0)
                            {
                                instance = instanceExists.First();
                            }
                            instance.Name = traceInstances.InstanceName;
                            instance.Authentication = traceInstances.Authentication;
                            instance.Login = traceInstances.Login;
                            instance.Password = traceInstances.Password;
                            instance.Port = traceInstances.Port;

                            string[] servername = instance.Name.Split('\\');

                            var server = from a in dc.PerfServers
                                         where a.Name == servername[0]
                                         select a;
                            Server_Trace server_trace = new Server_Trace();
                            if (server.Count() == 0)
                            {
                                PerfServer perfserver = new PerfServer();
                                perfserver.Name = servername[0];
                                dc.PerfServers.InsertOnSubmit(perfserver);
                                instance.PerfServer = perfserver;
                                server_trace.PerfServer = perfserver;
                                server_trace.Trace = trace;
                                dc.Server_Traces.InsertOnSubmit(server_trace);

                                dc.Instances.InsertOnSubmit(instance);

                            }
                            else
                            {
                                instance.PerfServer = server.First();

                                //dc.Instances.InsertOnSubmit(instance);
                                var traceExists = from a in dc.Server_Traces
                                                  where a.TraceID == trace.TraceID
                                                  && a.PerfServerID == server.First().PerfServerID
                                                  select a;
                                if (traceExists.Count() == 0)
                                {
                                    server_trace.PerfServer = server.First();
                                    server_trace.Trace = trace;
                                    dc.Server_Traces.InsertOnSubmit(server_trace);
                                }

                            }
                            dc.SubmitChanges();
                            InstanceTrace.TraceID = trace.TraceID;
                            InstanceTrace.InstanceID = instance.InstanceID;
                            dc.Instance_Traces.InsertOnSubmit(InstanceTrace);
                            dc.SubmitChanges();

                        }



                        new SchedulePerfTask().CreateTask(trace.TraceID, Convert.ToDateTime(trace.StartTime), Convert.ToDateTime(trace.StopTime));
                        //Convert.ToInt32(((System.Windows.Controls.Primitives.Selector)(sender)).SelectedItem);
                        MessageBox.Show("Performance trace " + trace.Name + " scheduled ");


                    }

                    else
                    {
                        MessageBox.Show("Start date cannot be less than current date");
                    }
                }
                else
                {
                    MessageBox.Show("Add some instances to the trace");

                }

            }
            else
            {
                MessageBox.Show("Use a different Trace name");
            }
        }

        private void btnaddnewpt_Click(object sender, RoutedEventArgs e)
        {
            SQLServerInstance SqlServerInstance = new SQLServerInstance();
            SqlServerInstance.InstanceName = "<New Instance>";
            SqlServerInstance.Port = "1433";
            SqlServerInstance.Authentication = "Windows Authentication";
            SqlServerInstance.Login = "";

            SqlServerInstance.Password = "";


            this.PerformanceTraceInstances.Insert(PerformanceTraceInstances.Count, SqlServerInstance);
            this.PerformanceTracingListbox.DataContext = this.PerformanceTraceInstances;

        }

        private void btnClearpt_Click(object sender, RoutedEventArgs e)
        {

            try
            {

                while (PerformanceTracingListbox.SelectedItems.Count > 0)
                {

                    this.PerformanceTraceInstances.Remove((SQLServerInstance)PerformanceTracingListbox.SelectedItem);


                }

                var bc = new BrushConverter();
                statusBar.Foreground = (Brush)bc.ConvertFrom("#003366");
                statusText.Text = "Ready.";

            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
            finally
            {




            }
        }

        private void btnRemoveallpt_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                this.PerformanceTraceInstances.Clear();

                var bc = new BrushConverter();
                statusBar.Foreground = (Brush)bc.ConvertFrom("#003366");
                statusText.Text = "Ready.";

            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
            finally
            {

                isRunning = false;


            }
        }

        private void TraceName_IsVisibleChanged(object sender, System.Windows.DependencyPropertyChangedEventArgs e)
        {

            var tracenames = from a in dc.Traces
                             select a;
            bool newadded = false;
            foreach (var item in tracenames)
            {

                bool add = true;
                foreach (var item2 in TraceName.Items)
                {
                    if (item2.Equals(Convert.ToString(item.Name)))
                    {
                        add = false;
                    }

                }
                if (add)
                {


                    TraceName.Items.Add(new ComboBoxItem().Content = Convert.ToString(item.Name));
                    newadded = true;
                }


            }
            if (newadded)
            {
                TraceName.SelectedIndex = TraceName.Items.Count - 1;
            }
        }

        private void TraceName_SelectionChanged(object sender, System.Windows.Controls.SelectionChangedEventArgs e)
        {
            if (TraceName.Visibility == Visibility.Visible)
            {
                Trace trace = new Trace();
                if (TraceName.SelectedIndex != -1)
                {
                    trace.TraceID = (from a in dc.Traces
                                     where a.Name == Convert.ToString(((System.Windows.Controls.Primitives.Selector)(sender)).SelectedItem)
                                     select a.TraceID).First();

                    PerformanceDashboard.RefreshReport(trace);
                    WaitstatsDashboard.RefreshReport(trace);

                }
            }
        }

        private void btnAddToPerformanceTrace_Click(object sender, RoutedEventArgs e)
        {



            foreach (var item in SQLServerInstances)
            {

                if (!containsInstances(PerformanceTraceInstances, item))
                {
                    PerformanceTraceInstances.Add(item);


                }
            }

        }

        private void Grid_Loaded(object sender, System.Windows.RoutedEventArgs e)
        {

            if (!datefilled)
            {
                StartHourCB.SelectedIndex = DateTime.Now.Hour;
                EndHourCB.SelectedIndex = DateTime.Now.Hour;
                EndMinCB.SelectedIndex = DateTime.Now.Minute;
                StartMinCB.SelectedIndex = DateTime.Now.Minute;
                EndDate.SelectedDate = DateTime.Now.AddDays(1).Date;
                TraceNametxtbx.Text = "Trace " + DateTime.Now.DayOfWeek + " " + DateTime.Now.Hour + ":" + DateTime.Now.Minute + " " + Char.ToUpper(DateTime.Now.ToString("MMMM")[0]) + DateTime.Now.ToString("MMMM").Substring(1) + " " + DateTime.Now.Year;
                datefilled = true;
            }

        }

        private void btnDeleteTrace_Click(object sender, System.Windows.RoutedEventArgs e)
        {
            try
            {
                var tracenames = from a in dc.Traces
                                 where a.Name == TraceName.SelectedItem
                                 select a;
                if (tracenames.Count() == 0)
                    MessageBox.Show("No trace selected");
                else
                {
                    MessageBoxResult r = MessageBox.Show("remove the trace " + tracenames.First().Name + "?", "Remove trace", MessageBoxButton.YesNo);
                    if (r == MessageBoxResult.Yes)
                    {

                        foreach (var item in (from a in dc.Server_Traces where a.TraceID == tracenames.First().TraceID select a))
                        {
                            dc.Server_Traces.DeleteOnSubmit(item);


                        }
                        foreach (var item in (from a in dc.Instance_Traces where a.TraceID == tracenames.First().TraceID select a))
                        {
                            dc.Instance_Traces.DeleteOnSubmit(item);
                        }
                        foreach (var item in (from a in dc.Times where a.TraceID == tracenames.First().TraceID select a))
                        {
                            foreach (var instancedata in (from a in dc.InstanceDatas where a.TimeID == item.TimeID select a))
                            {
                                dc.InstanceDatas.DeleteOnSubmit(instancedata);

                            }
                            foreach (var databasedata in (from a in dc.PerfDatabaseDatas where a.TimeID == item.TimeID select a))
                            {
                                dc.PerfDatabaseDatas.DeleteOnSubmit(databasedata);

                            }
                            foreach (var diskio in (from a in dc.DiskIOs where a.TimeID == item.TimeID select a))
                            {
                                dc.DiskIOs.DeleteOnSubmit(diskio);

                            }
                            foreach (var diskio in (from a in dc.DiskIOs where a.TimeID == item.TimeID select a))
                            {
                                dc.DiskIOs.DeleteOnSubmit(diskio);

                            }
                            foreach (var cpuUsage in (from a in dc.CpuUsages where a.TimeID == item.TimeID select a))
                            {
                                dc.CpuUsages.DeleteOnSubmit(cpuUsage);

                            }
                            foreach (var waitstats in (from a in dc.PerfWaitstatsDatas where a.TimeID == item.TimeID select a))
                            {
                                dc.PerfWaitstatsDatas.DeleteOnSubmit(waitstats);

                            }
                            dc.Times.DeleteOnSubmit(item);

                        }

                        new SchedulePerfTask().removetask(tracenames.First().TraceID);

                        dc.Traces.DeleteOnSubmit(tracenames.First());
                        dc.SubmitChanges();
                        TraceName.Items.RemoveAt(TraceName.SelectedIndex);
                        if (TraceName.SelectedIndex == -1 && TraceName.Items.Count > 0)
                        {

                            TraceName.SelectedIndex++;
                        }

                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
            }
        }



    }
}

